Checking column for duplicate values and appending

Hi -

Thanks in advance for taking a look at this complicated problem!

I have a column of numbers in Column A. The numbers in column A may or may not be unique (some are duplicates). Each number in column A has a corresponding value in column B. I'd like to start in cell A1 and check cell A2's value. If a1=a2, I'd like the value in b2 to be appended to whatever value is in b1 (with a comma separator). Then I'd like to check to see if a2=a3. If it does, I'd also like this appended to b1. If its not equal, I'd like check a3 against a4. if it's equal append the value of b4 to b3, etc.. If possible, I'd like to replace the values I'm appending and delete them from their current cell. It might be easier to show an example:

I'd appreciate any / all help!

BEFORE:
Column A Column B
01111109 EAGF
01111109 EAOF
01111109 EBOE
01111154 EAGF
01111154 EAOF
01111166 EAGF
01111166 EALZ

AFTER
Column A Column B
01111109 EAGF,EAOF,EB0E
01111154 EAGF,EAOF
01111166 EAGF,EALZ

etc. Column A is actually over 5,000 rows long.


Thanks in advance, Lee

Hi -

Thank you VERY much for the reply. When I run the macro, I receive the following :

Microsoft Visual Basic

Run-time error '-2147417848 (80010108)':

Method of 'Transpose' of object 'Worksheetfunction' failed

Did I do something wrong?

Not sure - could you attach a small sample of your data?

Never mind - my fault. I was using an earlier version of the spreadsheet that had multiple worksheets. Your VB script worked like a champ!!!!! I can't tell you how much I truly appreciate your help.

I was trying to do this manually - cutting and pasting. Your help saved me several hours of work tonight, as I needed this for tomorrow. Thanks again!!!!

Lee