How to concatenate values in column with same primary key?

[burghman217]

Essentially, I have a worksheet that looks like this:

Record ID	Candidate ID	First Name	Last Name	FirstNameLastName	Skill Record ID	Skill Name
7064096	1204825	Jamie	Burkholder	Jamie Burkholder	550001252	Operations
7064097	1204825	Jamie	Burkholder	Jamie Burkholder	550002280	Purchasing
7064098	1204825	Jamie	Burkholder	Jamie Burkholder	550001513	Bartender
7064099	1204825	Jamie	Burkholder	Jamie Burkholder	550001254	Sales
7159947	1213663	Stacey	Mccorkle	Stacey Mccorkle	550002187	Pharmacist
7159948	1213663	Stacey	Mccorkle	Stacey Mccorkle	550001651	Compliance
7159949	1213663	Stacey	Mccorkle	Stacey Mccorkle	550001746	Dispensing
7159950	1213663	Stacey	Mccorkle	Stacey Mccorkle	550001957	Inventory
7159951	1213663	Stacey	Mccorkle	Stacey Mccorkle	550001254	Sales
7159952	1213663	Stacey	Mccorkle	Stacey Mccorkle	550001218	Writing
7159953	1213663	Stacey	Mccorkle	Stacey Mccorkle	550001662	Consultant

<tbody>
</tbody>

but I need to get it into the following format:

Record ID	Candidate ID	First Name	Last Name	FirstNameLastName	Skills
7064096	1204825	Jamie	Burkholder	Jamie Burkholder	Operations, Purchasing, Bartender, Sales
7159947	1213663	Stacey	Mccorkle	Stacey Mccorkle	Pharmacist, Compliance, Dispensing, Inventory, Sales, Writing, Consultant

<tbody>
</tbody>

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