Find duplicates and merge adjacent data for number of that duplicate into one cell

Hello,

I hope you all had a nice Easter Break! I have a bit of an Excel hairy one which I am hoping you maybe able to help with.

I pull data out of our system with just 2 columns Position ID and Position Title.

The Position Title is repeated several times for each position ID linked to it i.e there are 4 position id's linked to the position title Accounts Payable Officer.

With over 4500 position ID's I need a help on a code/macro that will loop through the position titles find the rows that contain duplicates and place all the position ID's of duplicates in the one cell (separated with Alt Enter) next to the first entry and then delete the other duplicates.

I currently have a manual concatenate (in column c) but we can't do this manually every month, it would take me a day to do the whole thing.


Any takers?

Thanks in advance for your consideration!

Sal.

Hi, please see attached workbook with Before and After sheet examples.

Hi,

Thanks for this Jerry it looks great. I get a "type Mismatch" or "Error 400" error message after the initial confirmmerge message box. It seems to have started working but stops half way through. I looked where it keeps stopping at and this is where some of the contractor numbers start which are just numbers they begin with a or a couple of letters. Could this be it and is there away around this? I also noticed that the contractor position titles had a dash in them so I deleted this and tried again to no avail. I am thinking it is likley the position ID's with letters.

Hi, Please see attached amended before after sheet. I have highlighted the ones it seems to stop at. I have deleted these few samples and it works but I have nearly 5000 records and I can't go through it one by one. Also when I look at the cell format compared to others I cannot see any nocible difference. The only thing I can see is that when you have one position title with many position ID,s it looks like the order of position ID's for that job title are in sequence but I tried sorting by position title then position ID but with the same issue. I am sorry to be a pain but any help on this would be greatly appreciated. I feel like I am missing something very obvious???

hi, jooga, please check attachment, press "Start", this is one of the options to make it

Yay JB your a legend! Thanks so much its working a treat.