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

Question

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.

Answer 1

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.