0

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.

3
  • Is this a one-time task? or should it be done regularly?
    – Akina
    Jul 8 '19 at 8:00
  • @Akina it is a one time task
    – alim1990
    Jul 8 '19 at 8:06
  • You need code to get the result.
    – Lee
    Jul 8 '19 at 9:41
4

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.

5
  • Please note that I have 10 field per row, not 3, but for simplicity I said it is 3 columns.
    – alim1990
    Jul 8 '19 at 8:40
  • Edit code, what a problem?
    – Akina
    Jul 8 '19 at 8:43
  • look, I have 150K. It only run on 20K and exit with an error of Overflow. Should I split my excel to 20K rows sheets?
    – alim1990
    Jul 8 '19 at 9:26
  • @alim1990 try to replace integer to long Jul 8 '19 at 9:32
  • Change iterational variables definitions from Integer to Long (i, and, maybe, crow).
    – Akina
    Jul 8 '19 at 9:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.