Hi VBAX masters
This is for someone who enjoys a challenge
..hope you can help
A work colleague of mine who knows very little about excel asked me to help him with this, however it is beyond my basic level of VBA skills, however as follows is what I have been able describe step by step by the way I think it might be done. As you might see it is a straight forward,...but I am sure there are heaps of VBAX members who can solve while blind foldered and with one hand tied behind your back....so here it is......
There is: 1 workbook containing 2 worksheets, and numbers of rows of info an be in the thousands
The task here is to create one list of addressees out of two sources of information – as you will see in the attached example file both sources are not formatted the same –also note that attached file also shows example of wanted results in column T of sheet 1 – (that is before it is tided up as per STEP3 below - by copying to anew worksheet and removing blank cells from the column in the new worksheet -.)
So here goes in terms as the way I am thinking about the probelm:
STEP 1
Compare Values in Column B in sheet1
if duplicated
then delete duplicates (Note: can be more than one duplicate)
STEP 2
NOW Row by row in sheet 1
compare the value remaining in column B of sheet 1 with all values listed in entire column B of sheet 2
If there is not a match then in sheet 1 output and reorder the string to Column T from Column C of sheet 1
Could be a number of cases sof how string will appear in source column B in sheet 1 – for example
In case 1 if in column C FLINTSTONE Fred
Then in column T “Fred FLINTSTONE”
Or if case 2 if in column C FLINTSTONE Fred, Wilma
Then in column T “Fred FLINTSTONE and Wilma FLINTSTONE”
Or if case 3 if in column C FLINTSTONE Fred, Wilma, RUBBLE Barney
Then in Column T “ Fred FLINTSTONE and Wilma FLINSTONE and Barney RUBBLE”
Of if case 4 if in Column C “FLINTSTONE Fred, Wilma, RUBBLE Barney, SLATTER, Joe
Then in Column T “Fred FLINTSTONE and Wilma FLINSTONE and Barney RUBBLE and Joe SLATTER”
‘NOTE – In the source column B of sheet 1 the surname will always be in CAPITALS and if more than one christian name after the same surname then the christian name will always be separated by a comma. However when there is a match and then the sourse comes from sheet 2 then the surname is not in capitals. The end result does not need to be in capitals.
IF there is a Match
Then DON’T use names from column B of sheet 1 but instead then use info from sheet 2 to concatenate (with appropriate single spacing) in Column T in sheet 1 a string from source sheet 2 columns C, D, and E for example attached (you will ahve to imagine that row ID numebrs in column B match fro FLINTSTONE )-then see how row 1 column T has come from sheet 2
And if in sheet 2 a value repeats (can repeat more than once) then also add to the just created string an additional concatenation being sourced from the row of the repeated value that has occurred in sheet 2 . see again in the attached example file how row 1 column T has come from the seconsheet 2
Step 3 Final step required - copy results from column T of sheet 1 to a new worksheet and in the new worksheet remove an blank cells from the copied column
I hope this explains the problem clear enough
Thanks for your help
Pharlap