How to concatenate values in column with same primary key?

[burghman217]

Essentially, I have a worksheet that looks like this:

[TABLE="width: 849"]
<tbody>[TR]
[TD]Record ID[/TD]
[TD]Candidate ID[/TD]
[TD]First Name[/TD]
[TD]Last Name[/TD]
[TD]FirstNameLastName[/TD]
[TD]Skill Record ID[/TD]
[TD]Skill Name[/TD]
[/TR]
[TR]
[TD]7064096[/TD]
[TD]1204825[/TD]
[TD]Jamie[/TD]
[TD] Burkholder[/TD]
[TD]Jamie Burkholder[/TD]
[TD]550001252[/TD]
[TD]Operations[/TD]
[/TR]
[TR]
[TD]7064097[/TD]
[TD]1204825[/TD]
[TD]Jamie[/TD]
[TD] Burkholder[/TD]
[TD]Jamie Burkholder[/TD]
[TD]550002280[/TD]
[TD]Purchasing[/TD]
[/TR]
[TR]
[TD]7064098[/TD]
[TD]1204825[/TD]
[TD]Jamie[/TD]
[TD] Burkholder[/TD]
[TD]Jamie Burkholder[/TD]
[TD]550001513[/TD]
[TD]Bartender[/TD]
[/TR]
[TR]
[TD]7064099[/TD]
[TD]1204825[/TD]
[TD]Jamie[/TD]
[TD] Burkholder[/TD]
[TD]Jamie Burkholder[/TD]
[TD]550001254[/TD]
[TD]Sales[/TD]
[/TR]
[TR]
[TD]7159947[/TD]
[TD]1213663[/TD]
[TD]Stacey[/TD]
[TD] Mccorkle[/TD]
[TD]Stacey Mccorkle[/TD]
[TD]550002187[/TD]
[TD]Pharmacist[/TD]
[/TR]
[TR]
[TD]7159948[/TD]
[TD]1213663[/TD]
[TD]Stacey[/TD]
[TD] Mccorkle[/TD]
[TD]Stacey Mccorkle[/TD]
[TD]550001651[/TD]
[TD]Compliance[/TD]
[/TR]
[TR]
[TD]7159949[/TD]
[TD]1213663[/TD]
[TD]Stacey[/TD]
[TD] Mccorkle[/TD]
[TD]Stacey Mccorkle[/TD]
[TD]550001746[/TD]
[TD]Dispensing[/TD]
[/TR]
[TR]
[TD]7159950[/TD]
[TD]1213663[/TD]
[TD]Stacey[/TD]
[TD] Mccorkle[/TD]
[TD]Stacey Mccorkle[/TD]
[TD]550001957[/TD]
[TD]Inventory[/TD]
[/TR]
[TR]
[TD]7159951[/TD]
[TD]1213663[/TD]
[TD]Stacey[/TD]
[TD] Mccorkle[/TD]
[TD]Stacey Mccorkle[/TD]
[TD]550001254[/TD]
[TD]Sales[/TD]
[/TR]
[TR]
[TD]7159952[/TD]
[TD]1213663[/TD]
[TD]Stacey[/TD]
[TD] Mccorkle[/TD]
[TD]Stacey Mccorkle[/TD]
[TD]550001218[/TD]
[TD]Writing[/TD]
[/TR]
[TR]
[TD]7159953[/TD]
[TD]1213663[/TD]
[TD]Stacey[/TD]
[TD] Mccorkle[/TD]
[TD]Stacey Mccorkle[/TD]
[TD]550001662[/TD]
[TD]Consultant[/TD]
[/TR]
</tbody>[/TABLE]

but I need to get it into the following format:

[TABLE="width: 912"]
<tbody>[TR]
[TD]Record ID[/TD]
[TD]Candidate ID[/TD]
[TD]First Name[/TD]
[TD]Last Name[/TD]
[TD]FirstNameLastName[/TD]
[TD]Skills[/TD]
[/TR]
[TR]
[TD]7064096[/TD]
[TD]1204825[/TD]
[TD]Jamie[/TD]
[TD] Burkholder[/TD]
[TD]Jamie Burkholder[/TD]
[TD]Operations, Purchasing, Bartender, Sales[/TD]
[/TR]
[TR]
[TD]7159947[/TD]
[TD]1213663[/TD]
[TD]Stacey[/TD]
[TD] Mccorkle[/TD]
[TD]Stacey Mccorkle[/TD]
[TD]Pharmacist, Compliance, Dispensing, Inventory, Sales, Writing, Consultant[/TD]
[/TR]
</tbody>[/TABLE]

The data is unfortunately hundreds of thousands of rows long, so it's not efficient to apply the TEXTJOIN function manually. Any ideas?

 

[Joe4]

Welcome to the Board!

Give this VBA code a try:

Sub CombineRecords()

    Dim myRow As Long
    
'   Enter which row data starts on (excluding header)
    myRow = 2
    
    Application.ScreenUpdating = False
    
'   Loop until out of data
    Do Until Cells(myRow, "A") = ""
'       Check to see if next row is for same person (using Candidate ID - column B)
        If Cells(myRow, "B") = Cells(myRow + 1, "B") Then
'           Add Skill name to end (column G)
            Cells(myRow, "G") = Cells(myRow, "G") & ", " & Cells(myRow + 1, "G")
'           Then delete row
            Rows(myRow + 1).Delete
        Else
'           Move down one row if no match
            myRow = myRow + 1
        End If
    Loop
            
    Application.ScreenUpdating = True
    
End Sub

 

[burghman217]

This was perfect! Thank you!

 

[Joe4]

You are welcome!

 

[burghman217]

I'm hoping you can actually help me with two more, similar scenarios (please let me know if I should be posting this to
a separate thread!).


Basically, I need the data from each row to roll up into separate, repeating columns based upon the primary key, Candidate ID (see images in link below). Is this possible?


Education & Work History - Album on Imgur

 

[Joe4]

I cannot see your image. Workplace security blocks those sites for me.

Basically, the rule of thumb is this:
- If it is a directly-related follow-up on the original question (where the answer to the original question is pertinent), then paste it in the same thread.
- If it is a brand new question that is not dependent upon the previous question/response, post it in a new thread.

 

[PradeepKhanna]

I solved it with a formula (I use O365, hence used TextJoin & XLOOKUP: you can use CONCAT & VLOOKUP if you are on older versions that do not support these functions: below is what the formula in Row 6 looks like

If you are expecting more than 3 records per data: This will give complete CONCAT in the 1st row of the concerned primary Key
=TEXTJOIN("; ",TRUE,[@[Skill Name]],XLOOKUP([@[Candidate ID]],B7:$B$13,I7:$I$13,""))
Basically, VLOOKUP Concatenated values of the target column from in rows below, and concat them all into a single row



If you are expecting upto 3 records per data: this will give complete concat in all records (EG. my data in the sheet
=TEXTJOIN(";",TRUE,[@[Skill Name]],XLOOKUP([@[Candidate ID]],$B$1:B5,$G$1:G5,""),XLOOKUP([@[Candidate ID]],B7:$B$15,G7:$G$15,""))

Basically, VLOOKUP values from the target column in rows above and rows below, and concat them all into a single row

Unable to upload pictures: Keep getting the message "Uploaded file is too large" for even a 500KB pic

 

[Peter_SSs]

you can use CONCAT & VLOOKUP if you are on older versions

Actually you couldn't since CONCAT is also only available in 365 and 2019+ versions.

 

[alansidman]

Can be accomplished with Power Pivot. Create a measure (DAX)

=CONCATENATEX(Range,Range[Skill Name],", ")

See this link for a tutorial-->Excel: Reporting Text in a Pivot Table - Strategic Finance