Can someone please help with a VBA that will recognise a unique id for the row and then combine info from multiple entries

[tbancroft]

Hi Excel Gurus!

I have an excel doc with 'customer information' whereby there may be multiple entries for each unique ID (or customer). The worksheet has some multiple rows which contain individual data entries (1 row may have an email address and unique ID, another may have a first and second name and the same unique ID).
I'm looking for a way to combine all the info so that each unique ID is only listed once, and all the relevant fields for that ID are collated.

If its relevant my sheet runs from 'A' to 'U' with the unique ID being in column A. The sheet is approx 50,000 rows.

Thanks in advance for your help!!

 

[Rick Rothstein]

Hi Excel Gurus!

I have an excel doc with 'customer information' whereby there may be multiple entries for each unique ID (or customer). The worksheet has some multiple rows which contain individual data entries (1 row may have an email address and unique ID, another may have a first and second name and the same unique ID).
I'm looking for a way to combine all the info so that each unique ID is only listed once, and all the relevant fields for that ID are collated.

If its relevant my sheet runs from 'A' to 'U' with the unique ID being in column A. The sheet is approx 50,000 rows.

Do you have a header row (if more than one, how many)?

Is your data sorted by Column A? If not, can it be?

Just checking... for each unique ID, from Column B onward, there is guaranteed to be only one entry per column no matter how many rows are occupied by the unique ID, correct?

 

[tbancroft]

Hi Rick,
1 header row
Yes, it is sorted by column A (descending)
No - Some of the cells may have repeat information, but the repeat info (if it is repeated) is the same in both cells

Thanks for your help!!

 

[Rick Rothstein]

No - Some of the cells may have repeat information, but the repeat info (if it is repeated) is the same in both cells

Okay, that kills the simple approach I was hoping to take. Let me see what I (or others) can come up with.

 

[Rick Rothstein]

See if this macro does what you want. Note that I assume your data is on Sheet1 (green text) and that you want your output to go to Sheet2 (red text, you might have to scroll down to see it)... change as needed (if you change the red text to the same sheet name as the green text, the new data will completely overwrite the old data).

Sub CombineDataPerUniqueID()
  Dim R As Long, RR As Long, C As Long, X As Long, Rw As Long, LastRow As Long
  Dim Data As Variant, Result As Variant
  With Sheets("[B][COLOR="#008000"]Sheet1[/COLOR][/B]")
    LastRow = .Columns("A:U").Find("*", , xlValues, , xlRows, xlPrevious).Row
    Data = .Range("A1:U" & LastRow + 1)
  End With
  ReDim Result(1 To UBound(Data), 1 To 21)
  For R = 2 To UBound(Data)
    If Data(R, 1) <> Data(R - 1, 1) Then
      X = X + 1
      Result(X, 1) = Data(R, 1)
      Result(X, 2) = R
    End If
  Next
  X = 0
  For R = 2 To UBound(Data)
    X = X + 1
    Rw = Result(X, 2)
    Result(X, 2) = ""
    For C = 2 To 21
      For RR = Rw To Result(X + 1, 2) - 1
        If Len(Data(RR, C)) Then
          Result(X, C) = Data(RR, C)
          Exit For
        End If
      Next
    Next
    R = Result(X + 1, 2)
  Next
  Result(X + 1, 2) = ""
  With Sheets("[B][COLOR="#FF0000"]Sheet2[/COLOR][/B]")
    .Cells.Clear
    .Range("A1").Resize(UBound(Result, 1), UBound(Result, 2)) = Result
  End With
End Sub

 

[tbancroft]

Rick!! Great work!!

Did everything i need it to... You are a legend!!