Link to home
Create AccountLog in
Avatar of finnstone

asked on

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
Avatar of Neil Fleming
Neil Fleming
Flag of United Kingdom of Great Britain and Northern Ireland image

Can you post your workbook? Code to do this will be relatively simple.
Avatar of finnstone


here is some of it, 100 rows
any ideas?
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

can this be run on 20k rows? its been working for 5 minutes
this doesnt work!
doesnt even work for the sample you provided.
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.


this should be

industry - Hospital Health Care; - Psychiatric, industry - Hospital Health Care, headline - Behavioral, industry - Hospital Health Care; - Psychiatric
Avatar of Neil Fleming
Neil Fleming
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account

amazing code
thank you. Sorry it didn't work properly first time around.. :)