Combine matches

[liampog]

Hi

On Sheet1, I have the following columns of data

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]DATE[/TD]
[TD]NUMBER[/TD]
[TD]NAME[/TD]
[TD]TYPE(S)[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]01/01/2019[/TD]
[TD]123[/TD]
[TD]J Bloggs[/TD]
[TD]A,B[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]01/01/2019[/TD]
[TD]456[/TD]
[TD]D Smith[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]01/01/2019[/TD]
[TD]789[/TD]
[TD]P Jones[/TD]
[TD]B,C[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]02/01/2019[/TD]
[TD]123[/TD]
[TD]J Bloggs[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]02/01/2019[/TD]
[TD]789[/TD]
[TD]P Jones[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]03/01/2019[/TD]
[TD]456[/TD]
[TD]D Smith[/TD]
[TD]A,B,C[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]03/01/2019[/TD]
[TD]789[/TD]
[TD]P Jones[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]03/01/2019[/TD]
[TD]000[/TD]
[TD]J Doe[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]03/01/2019[/TD]
[TD]123[/TD]
[TD]J Bloggs[/TD]
[TD]A,C[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]04/01/2019[/TD]
[TD]456[/TD]
[TD]D Smith[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]05/01/2019[/TD]
[TD]123[/TD]
[TD]J Bloggs[/TD]
[TD]A,B[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]05/01/2019[/TD]
[TD]456[/TD]
[TD]D Smith[/TD]
[TD]B,D[/TD]
[/TR]
</tbody>[/TABLE]


On Sheet2, I have the desired output shown below.

I have already created VBA that copies the B column from Sheet1 to Sheet2 and then removes duplicates to create a unique list of numbers. I then have a VLOOKUP set up to capture the name associated with the number. My problem is Column C on Sheet2.

I want to combine all of the Types from each customer, removing any duplicate entries.

The desired output is below and you can cross-reference this to Sheet1

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]NUMBER[/TD]
[TD]NAME[/TD]
[TD]TYPE(S)[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]123[/TD]
[TD]J Bloggs[/TD]
[TD]A,B,C[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]456[/TD]
[TD]D Smith[/TD]
[TD]A,B,C,D[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]789[/TD]
[TD]P Jones[/TD]
[TD]B,C,D[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]000[/TD]
[TD]J Doe[/TD]
[TD]A[/TD]
[/TR]
</tbody>[/TABLE]



Is there a formula that can achieve this? I'm sure there probably is but my knowledge of Excel formulas isn't amazing.

Thanks

 

[liampog]

How about the VBA code you provided prior? Editing it down so that it only focuses on extracting the Type data from Sheet1 that corresponds to the unique Number in Column A on Sheet2?

The VBA you provided works perfectly, but I don't need it to do anything about the Number and Name as this is all handled with VLOOKUPs.

I think I can create a Worksheet_Change event on Sheet1 that fires the code you gave whenever any Type data is changed.

I don't know anywhere near enough about VBA to be able to edit your VBA down to only do the above.

Thanks in advance
Liam

Sub liampog()   Dim Cl As Range
   Dim Dic As Object
   Dim Ky As Variant
   Dim i As Long
   
   Set Dic = CreateObject("scripting.dictionary")
   For Each Cl In Range("B2", Range("B" & Rows.Count).End(xlUp))
      If Not Dic.Exists(Cl.Value) Then
         Dic.Add Cl.Value, Array(Cl.Offset(, 1).Value, CreateObject("system.collections.arraylist"))
      End If
      Ky = Split(Cl.Offset(, 2).Value, ",")
      For i = 0 To UBound(Ky)
         With Dic(Cl.Value)(1)
            If Not .contains(CStr(Ky(i))) Then .Add CStr(Ky(i))
         End With
      Next i
   Next Cl
   i = 1
   For Each Ky In Dic.Keys
      i = i + 1
      Dic(Ky)(1).Sort
      Sheets("sheet2").Range("A" & i).Resize(, 3).Value = Array(Ky, Dic(Ky)(0), Join(Dic(Ky)(1).toarray, ","))
   Next Ky
End Sub

 

[Fluff]

Surely if somebody adds more data to sheet1 you will need to rerun your existing macro incase there is a new number in col B. In which case you can simply run my initial code which will do everything.

 

[liampog]

No it's not, but similar
Although I forgot a bit, use

Sub liampog()
   Dim Cl As Range
   Dim Dic As Object
   Dim Ky As Variant
   Dim i As Long
   
   Set Dic = CreateObject("scripting.dictionary")
   For Each Cl In Range("B2", Range("B" & Rows.Count).End(xlUp))
      If Not Dic.Exists(Cl.Value) Then
         Dic.Add Cl.Value, Array(Cl.Offset(, 1).Value, CreateObject("system.collections.arraylist"))
      End If
      Ky = Split(Cl.Offset(, 2).Value, ",")
      For i = 0 To UBound(Ky)
         With Dic(Cl.Value)(1)
            If Not .contains(CStr(Ky(i))) Then .Add CStr(Ky(i))
         End With
      Next i
   Next Cl
   i = 1
   For Each Ky In Dic.Keys
      i = i + 1
      Dic(Ky)(1).Sort
      Sheets("sheet2").Range("A" & i).Resize(, 3).Value = Array(Ky, Dic(Ky)(0), Join(Dic(Ky)(1).toarray, ","))
   Next Ky
End Sub

Fluff

I used this solution in the end, however, is there any way of it just pulling the unique number in Column B of Sheet 1 and combining the unique values in Column D of Sheet 1 into Column A of Sheet2 and Column E respectively?

Thanks in advance.
Liam

 

[Fluff]

How about

Sub liampog()
   Dim Cl As Range
   Dim Dic As Object
   Dim Ky As Variant
   Dim i As Long
   
   Set Dic = CreateObject("scripting.dictionary")
   For Each Cl In Range("B2", Range("B" & Rows.Count).End(xlUp))
      If Not Dic.Exists(Cl.Value) Then
         Dic.Add Cl.Value, CreateObject("system.collections.arraylist")
      End If
      Ky = Split(Cl.Offset(, 2).Value, ",")
      For i = 0 To UBound(Ky)
         With Dic(Cl.Value)
            If Not .contains(CStr(Ky(i))) Then .Add CStr(Ky(i))
         End With
      Next i
   Next Cl
   i = 1
   For Each Ky In Dic.keys
      i = i + 1
      Dic(Ky).Sort
      Sheets("sheet2").Range("A" & i).Value = Ky
      Sheets("sheet2").Range("E" & i).Value = Join(Dic(Ky).toarray, ",")
   Next Ky
End Sub

 

[liampog]

That's perfect, thank you.

Liam

 

[Fluff]

You're welcome & thanks for the feedback