Combining data

liampog

Active Member
Joined
Aug 3, 2010
Messages
294
Hi

On Sheet1 I have the following pieces of data:

ABC
1DateNumberColour
213/09/2019123blue,green
313/09/2019456green,red
413/09/2019789red
514/09/2019123green
614/09/2019456red,blue
714/09/2019789red,green
815/09/2019123blue,red
915/09/2019456yellow,green
1015/09/2019789blue
1115/09/2019999red,blue

<tbody>
</tbody>

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

AB
1NumberColours
2123blue,green,red
3456green,red,blue,yellow,green
4789red,green,blue
5999red,blue

<tbody>
</tbody>


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
 
Last edited:

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
4,314
you can try Power Query

DateNumberColourNumberColours
13/09/2019​
123​
blue
123​
blue,green
13/09/2019​
456​
green
456​
green,red,yellow
13/09/2019​
789​
red
789​
red,blue
14/09/2019​
123​
green
999​
red
14/09/2019​
456​
red
14/09/2019​
789​
red
15/09/2019​
123​
blue
15/09/2019​
456​
yellow
15/09/2019​
789​
blue
15/09/2019​
999​
red

Code:
[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
 
Last edited:

Forum statistics

Threads
1,086,099
Messages
5,387,825
Members
402,081
Latest member
PiotrX

Some videos you may like

This Week's Hot Topics

Top