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

 

[offthelip]

looks like an ideal example where using a dictionary of dictionaries would solve your problem.

 

[sandy666]

you can try Power Query aka Get&Transform

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]DATE[/td][td=bgcolor:#5B9BD5]NUMBER[/td][td=bgcolor:#5B9BD5]NAME[/td][td=bgcolor:#5B9BD5]TYPE(S)[/td][td][/td][td=bgcolor:#70AD47]NUMBER[/td][td=bgcolor:#70AD47]NAME[/td][td=bgcolor:#70AD47]TYPE[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

01/01/2019​

[/td][td=bgcolor:#DDEBF7]

123​

[/td][td=bgcolor:#DDEBF7]J Bloggs[/td][td=bgcolor:#DDEBF7]A,B[/td][td][/td][td=bgcolor:#E2EFDA]123[/td][td=bgcolor:#E2EFDA]J Bloggs[/td][td=bgcolor:#E2EFDA]A,B,C[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

01/01/2019​

[/td][td]

456​

[/td][td]D Smith[/td][td]A[/td][td][/td][td]456[/td][td]D Smith[/td][td]A,B,C,D[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

01/01/2019​

[/td][td=bgcolor:#DDEBF7]

789​

[/td][td=bgcolor:#DDEBF7]P Jones[/td][td=bgcolor:#DDEBF7]B,C[/td][td][/td][td=bgcolor:#E2EFDA]789[/td][td=bgcolor:#E2EFDA]P Jones[/td][td=bgcolor:#E2EFDA]B,C,D[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

02/01/2019​

[/td][td]

123​

[/td][td]J Bloggs[/td][td]B[/td][td][/td][td]000[/td][td]J Doe[/td][td]A[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

02/01/2019​

[/td][td=bgcolor:#DDEBF7]

789​

[/td][td=bgcolor:#DDEBF7]P Jones[/td][td=bgcolor:#DDEBF7]D[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

03/01/2019​

[/td][td]

456​

[/td][td]D Smith[/td][td]A,B,C[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

03/01/2019​

[/td][td=bgcolor:#DDEBF7]

789​

[/td][td=bgcolor:#DDEBF7]P Jones[/td][td=bgcolor:#DDEBF7]C[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

03/01/2019​

[/td][td]000[/td][td]J Doe[/td][td]A[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

03/01/2019​

[/td][td=bgcolor:#DDEBF7]

123​

[/td][td=bgcolor:#DDEBF7]J Bloggs[/td][td=bgcolor:#DDEBF7]A,C[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

04/01/2019​

[/td][td]

456​

[/td][td]D Smith[/td][td]A[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

05/01/2019​

[/td][td=bgcolor:#DDEBF7]

123​

[/td][td=bgcolor:#DDEBF7]J Bloggs[/td][td=bgcolor:#DDEBF7]A,B[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

05/01/2019​

[/td][td]

456​

[/td][td]D Smith[/td][td]B,D[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"DATE", type datetime}, {"NUMBER", type text}, {"NAME", type text}, {"TYPE(S)", type text}}),
    ROC = Table.SelectColumns(Type,{"NUMBER", "NAME", "TYPE(S)"}),
    Split = Table.ExpandListColumn(Table.TransformColumns(ROC, {{"TYPE(S)", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "TYPE(S)"),
    Group = Table.Group(Split, {"NUMBER", "NAME"}, {{"Count", each _, type table}}),
    List = Table.AddColumn(Group, "TYPE", each List.Distinct(Table.Column([Count],"TYPE(S)"))),
    Extract = Table.TransformColumns(List, {"TYPE", each Text.Combine(List.Transform(_, Text.From), ","), type text})
in
    Extract[/SIZE]

 

[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, 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
      Sheets("sheet2").Range("A" & i).Resize(, 3).Value = Array(Ky, Dic(Ky)(0), Join(Dic(Ky)(1).toarray, ","))
   Next Ky
End Sub

 

[liampog]

Hi

I've just realised I asked this question before. Sorry!

Thanks for your answers. I'll have a look at them. The last answer looks interesting. I assume this is a "Dictionary of Dictionaries" mentioned in the first answer, which I've never heard of before!

Thanks
Liam

 

[Fluff]

I assume this is a "Dictionary of Dictionaries" mentioned in the first answer,

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

 

[liampog]

From my OP:

Is there a formula that can achieve this?

Whilst this VBA code works perfectly, what I really wanted is some kind of lookup formula that achieves the same thing so that if the data in the Type column is changed on Sheet1, it reflects in the output automatically on Sheet2 without the need for any VBA code firing.

If there is no formula, or combination of formulas, that achieves the desired outcome, the VBA code you've detailed will be a workaround that I can put in perhaps run if with a Worksheet_Change event on any cells in the Type column on Sheet1.

Thanks for your help,
Liam

 

[liampog]

I also forgot to say that I only need formulas and/or VBA code to help me with Column C on Sheet2 (the Types column). The number and name columns are already sorted.

Just a way to lookup and combine unique values from Types.

Thanks in advance,
Liam

 

[Fluff]

Formulae are not my strong point, so not sure if it can be done that way, but if it is possible it would probably be a very complex formula.
However you could use this UDF

Function liampog(Rng As Range, Crit As String) As String
   Dim Cl As Range
   Dim Lst As Object
   Dim Sp As Variant
   Dim i As Long
   
   Set Lst = CreateObject("system.collections.arraylist")
   For Each Cl In Rng
      If Cl.Value = Crit Then
         Sp = Split(Cl.Offset(, 2), ",")
         For i = 0 To UBound(Sp)
            If Not Lst.contains(CStr(Sp(i))) Then Lst.Add CStr(Sp(i))
         Next i
      End If
   Next Cl
   Lst.Sort
   liampog = Join(Lst.toarray, ",")
End Function

Used like
=liampog(Sheet1!$B$2:$B$13,A1)

 

[liampog]

Hi Fluff

Thanks for the reply.

Whilst this works perfectly, it processes extremely slowly, to the point where Excel goes into Not Responding mode until it's finished, and this is just for ONE formula.

The next part of my VBA code will be autofilling this formula down as far as there is data in the first column. This could potentially be several hundred rows of data.

Do you have any other suggestions? Or should it be taking this long?

Thanks
Liam