combine two columns if same value

i DOCID  in column A


in column AO - Behavioral - psych - Behavioral - Behavioral

I want to combine the values in AO where the values are the same in column A

so values would become this (only need to do it for the first one) - Behavioral - psych, - Behavioral - Behavioral, - Behavioral

NOTE, it is sorted on column A , and the tricky part is some have 3 matches, other have 4, 5 , 6 etc

I WOULD ALSO need a value put in column AP so i know which rows got appended more data
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Neil FlemingConsultant and developerCommented:
Can you post your workbook? Code to do this will be relatively simple.
finnstoneAuthor Commented:
here is some of it, 100 rows
finnstoneAuthor Commented:
any ideas?
Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

Neil FlemingConsultant and developerCommented:
try this:

Click button to routine below:
Sub Combine()
Dim rSource As Range, rComp As Range, rTarget As Range

'set source for id
Set rSource = ActiveSheet.Range("a2")

'set target cell
'Set rTarget = ActiveSheet.Range("ao2")
Set rTarget = ActiveSheet.Range("ao2")

'set cell to compare
Set rComp = rSource
    'move comparison cell down one row
    Set rComp = rComp.Offset(1, 0)
        If rComp = rSource Then
        'add to target cell
        rTarget = rTarget & ", " & rTarget.Offset(rComp.Row - rSource.Row, 0)
        Set rComp = rComp.Offset(-1, 0)
        'delete row where DOCID is identical
        rComp.Offset(1, 0).EntireRow.Delete
        End If
    'loop until blank cell
    Loop Until rComp = ""
'next source row
Set rSource = rSource.Offset(1, 0)
'loop till blank
Loop Until rSource = ""

End Sub

Open in new window

finnstoneAuthor Commented:
can this be run on 20k rows? its been working for 5 minutes
finnstoneAuthor Commented:
this doesnt work!
finnstoneAuthor Commented:
doesnt even work for the sample you provided.
Neil FlemingConsultant and developerCommented:
Strange. I just re-downloaded, and ran it. Can you explain what it is it that is not working? (It was your sample, btw.)

I get the attached result after running the code. The result has no identical keys in column A, and all the results collated in column AO as requested.
finnstoneAuthor Commented:


this should be

industry - Hospital Health Care; - Psychiatric, industry - Hospital Health Care, headline - Behavioral, industry - Hospital Health Care; - Psychiatric
Neil FlemingConsultant and developerCommented:
Ah. There was a missing line of code that was not keeping the "target" in synch with the "source". Apologies.

Also it wasn't handling blank entries properly.

Try the revised attached.

And yes, it will run on 20,000 lines, but probably quite slowly. It would be much faster if we could sort the rows by ID first, but not sure if you want to do that.

I've set it to scroll to and select the row it is currently working on so you can see progress.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
finnstoneAuthor Commented:

finnstoneAuthor Commented:
amazing code
Neil FlemingConsultant and developerCommented:
thank you. Sorry it didn't work properly first time around.. :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.