combine two columns if same value

finnstone
finnstone used Ask the Experts™
on
i DOCID  in column A

1111111111
44444
44444
8888888


in column AO
experience.organization.name - Behavioral
experience.organization.name - psych
experience.organization.name - Behavioral
experience.organization.name - 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)

experience.organization.name - Behavioral
experience.organization.name - psych, experience.organization.name - Behavioral
experience.organization.name - Behavioral,
experience.organization.name - 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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Neil FlemingConsultant and developer

Commented:
Can you post your workbook? Code to do this will be relatively simple.

Author

Commented:
here is some of it, 100 rows
Book1.xlsx

Author

Commented:
any ideas?
Neil FlemingConsultant and developer

Commented:
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")

Do
'set cell to compare
Set rComp = rSource
    Do
    '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

combine-columns.xlsm

Author

Commented:
can this be run on 20k rows? its been working for 5 minutes

Author

Commented:
this doesnt work!

Author

Commented:
doesnt even work for the sample you provided.
Neil FlemingConsultant and developer

Commented:
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.
combine-columns2.xlsm

Author

Commented:
thx.

USbc4852fa730f2e61


this should be

industry - Hospital Health Care; experience.organization.name - Psychiatric, industry - Hospital Health Care, headline - Behavioral, industry - Hospital Health Care; experience.organization.name - Psychiatric
Consultant and developer
Commented:
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.
combine-columns3.xlsm

Author

Commented:
EXCELLENT

Thanks

Author

Commented:
amazing code
Neil FlemingConsultant and developer

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial