Combine matches

liampog

Active Member
Joined
Aug 3, 2010
Messages
316
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
looks like an ideal example where using a dictionary of dictionaries would solve your problem.
 
Last edited:
Upvote 0
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]


Code:
[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]
 
Upvote 0
How about
Rich (BB code):
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
 
Last edited:
Upvote 0
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
 
Upvote 0
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
Rich (BB code):
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
 
Upvote 0
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

 
Last edited:
Upvote 0
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
 
Upvote 0
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
Code:
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)
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,224,875
Messages
6,181,513
Members
453,050
Latest member
Obil

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top