Combining data

liampog

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

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
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:
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,064
Members
448,545
Latest member
kj9

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