Excel How to combine all rows having the same id number into one column for a huge dataset?

I have a 350K of rows in an excel file. I need to combine all rows having a unique id to the same row as following:

Unique ID | Mbr ID | Name
1 | 10227 | ABC1
1 | 254112 | ABC2
2 | 3688 | XYZ1
2 | 1123 | XYZ2
2 | 9800 | XYZ3

For a reason, we need to combine fields having the same unique id into one like the following:

Unique ID | Mbr ID | Name Unique ID | Mbr ID | Name Unique ID | Mbr ID | Name
1 | 10227 | ABC1 1 | 254112 | ABC2
2 | 3688 | XYZ1 2 | 1123 | XYZ2 2 | 9800 | XYZ3 

We tried using vLookup but it seems to take a long time and in most case it's not working at all. Any idea on how to do it?

P.S. We tried a tool called Kutools but it didn't work either.

1 Answer

Assume the source data posessed on the worksheet named as Sheet1 occupied A1:Cxxxxxx range.

Create a worksheed names Sheet2 - the pivotted data will be placed into it.

Sort source data by column A (Unique ID).

Then run the next macro:

Sub doPivot()
Dim sh As Worksheet
Dim vals()
Dim i As Integer
Dim uid
Dim crow As Integer
Dim ccol As Integer
Set sh = ThisWorkbook.Sheets("Sheet2")
vals = ThisWorkbook.Sheets("Sheet1").UsedRange.Value
For i = LBound(vals, 1) To UBound(vals, 1) If vals(i, 1) = uid Then ccol = ccol + 3 Else crow = crow + 1 ccol = 1 End If uid = vals(i, 1) sh.Cells(crow, ccol) = vals(i, 1) sh.Cells(crow, ccol + 1) = vals(i, 2) sh.Cells(crow, ccol + 2) = vals(i, 3)
Next i
End Sub

Finally grad-over the header to left if needed.