Combining data

[liampog]

Hi

On Sheet1 I have the following pieces of data:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Date[/TD]
[TD]Number[/TD]
[TD]Colour[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]13/09/2019[/TD]
[TD]123[/TD]
[TD]blue,green[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]13/09/2019[/TD]
[TD]456[/TD]
[TD]green,red[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]13/09/2019[/TD]
[TD]789[/TD]
[TD]red[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]14/09/2019[/TD]
[TD]123[/TD]
[TD]green[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]14/09/2019[/TD]
[TD]456[/TD]
[TD]red,blue[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]14/09/2019[/TD]
[TD]789[/TD]
[TD]red,green[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]15/09/2019[/TD]
[TD]123[/TD]
[TD]blue,red[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]15/09/2019[/TD]
[TD]456[/TD]
[TD]yellow,green[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]15/09/2019[/TD]
[TD]789[/TD]
[TD]blue[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]15/09/2019[/TD]
[TD]999[/TD]
[TD]red,blue[/TD]
[/TR]
</tbody>[/TABLE]

On Sheet2 I want to set up a lookup that produces the following results:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Number[/TD]
[TD]Colours[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]123[/TD]
[TD]blue,green,red[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]456[/TD]
[TD]green,red,blue,yellow,green[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]789[/TD]
[TD]red,green,blue[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]999[/TD]
[TD]red,blue[/TD]
[/TR]
</tbody>[/TABLE]


You'll see that it ignores the date and combines the colours into one cell - ignoring duplicate values - and separates each colour with a comma.

At the moment I have some VBA code firing that copies the Numbers, Removes the Duplicates and then Copies the Numbers to Sheet2. It then looks row by row and combines the colours, however, I'm sure there might be a formula that might do the same thing by looking at the Number.

This would remove lines of programming code that perhaps are not necessary and reduce the time it takes for that code to run.

Thanks in advance if anyone can help.

Liam

 

[sandy666]

you can try Power Query

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Date[/td][td=bgcolor:#5B9BD5]Number[/td][td=bgcolor:#5B9BD5]Colour[/td][td][/td][td=bgcolor:#70AD47]Number[/td][td=bgcolor:#70AD47]Colours[/td][/tr]

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

13/09/2019​

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

123​

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

123​

[/td][td=bgcolor:#E2EFDA]blue,green[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

13/09/2019​

[/td][td]

456​

[/td][td]green[/td][td][/td][td]

456​

[/td][td]green,red,yellow[/td][/tr]

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

13/09/2019​

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

789​

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

789​

[/td][td=bgcolor:#E2EFDA]red,blue[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

14/09/2019​

[/td][td]

123​

[/td][td]green[/td][td][/td][td]

999​

[/td][td]red[/td][/tr]

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

14/09/2019​

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

456​

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

[tr=bgcolor:#FFFFFF][td]

14/09/2019​

[/td][td]

789​

[/td][td]red[/td][td][/td][td][/td][td][/td][/tr]

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

15/09/2019​

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

123​

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

[tr=bgcolor:#FFFFFF][td]

15/09/2019​

[/td][td]

456​

[/td][td]yellow[/td][td][/td][td][/td][td][/td][/tr]

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

15/09/2019​

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

789​

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

[tr=bgcolor:#FFFFFF][td]

15/09/2019​

[/td][td]

999​

[/td][td]red[/td][td][/td][td][/td][td][/td][/tr]
[/table]

[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"Number"}, {{"Count", each _, type table}}),
    List = Table.AddColumn(Group, "Colours", each List.Distinct(Table.Column([Count],"Colour"))),
    Extract = Table.TransformColumns(List, {"Colours", each Text.Combine(List.Transform(_, Text.From), ","), type text})
in
    Extract[/SIZE]

this is NOT a vba