extract a list of colour combinations from a table

merlin777

Well-known Member
Joined
Aug 29, 2009
Messages
1,395
Office Version
  1. 2007
I have a table of which this is an extract:

designforegroundbackgroundsizetype
plainwhiteblackXLT-shirt
plainwhiteblackMT-shirt
plainbrownyellowLcap
paisleyblueyellowLcap
paisleyblueyellowMT-shirt

<colgroup><col><col><col><col span="2"></colgroup><tbody>
</tbody>


I want a list of the colour combinations used e.g.:

white black
brown yellow
blue yellow

and a list of the colour combinations for each design eg:

plain:
white black
brown yellow

paisley:
blue yellow

I need them listed in a column each. I know can use a data filter but it needs to be dynamic and not manual.

Does excel have a function for this already?
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,379
Office Version
  1. 365
Platform
  1. Windows
Excel has new functions in Excel365 Insider. They do exactly what you wish and they do it in a jiffy. They are dynamic and they are functions that 'spill'.

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="background-color: #FFF2CC;;">design</td><td style="background-color: #FFF2CC;;">foreground</td><td style="background-color: #FFF2CC;;">background</td><td style="background-color: #FFF2CC;;">size</td><td style="background-color: #FFF2CC;;">type</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">plain</td><td style=";">white</td><td style=";">black</td><td style=";">XL</td><td style=";">T-shirt</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">plain</td><td style=";">white</td><td style=";">black</td><td style=";">M</td><td style=";">T-shirt</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">plain</td><td style=";">brown</td><td style=";">yellow</td><td style=";">L</td><td style=";">cap</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">paisley</td><td style=";">blue</td><td style=";">yellow</td><td style=";">L</td><td style=";">cap</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">paisley</td><td style=";">blue</td><td style=";">yellow</td><td style=";">M</td><td style=";">T-shirt</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="background-color: #FCE4D6;;">colour combinations used</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="background-color: #E2EFDA;;">white black</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">brown yellow</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">blue yellow</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style="background-color: #FCE4D6;;">colour combinations per design</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style="font-weight: bold;background-color: #E2EFDA;;">plain</td><td style="font-weight: bold;;">paisley</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style="background-color: #C6E0B4;;">white black</td><td style="background-color: #C6E0B4;;">blue yellow</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">18</td><td style=";">brown yellow</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:5.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet38</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A10</th><td style="text-align:left">=UNIQUE(<font color="Blue">B2:B6&" "&C2:C6</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A16</th><td style="text-align:left">=TRANSPOSE(<font color="Blue">UNIQUE(<font color="Red">A2:A6</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A17</th><td style="text-align:left">=UNIQUE(<font color="Blue">FILTER(<font color="Red">$B$2:$B$6&" "&$C$2:$C$6,$A$2:$A$6=A16</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B17</th><td style="text-align:left">=UNIQUE(<font color="Blue">FILTER(<font color="Red">$B$2:$B$6&" "&$C$2:$C$6,$A$2:$A$6=B16</font>)</font>)</td></tr></tbody></table></td></tr></table><br /><strike>
</strike>
[/FONT]
 
Last edited:

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
with PowerQuery (free add-in for Excel 2010 from MS site, 2007 doesn't support PQ)

designforegroundbackgroundsizetypedesignColoursColours
plainwhiteblackXLT-shirtplainwhite black, brown yellowwhite black
plainwhiteblackMT-shirtpaisleyblue yellowbrown yellow
plainbrownyellowLcapblue yellow
paisleyblueyellowLcap
paisleyblueyellowMT-shirt

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

Code:
[SIZE=1]// Colours
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Merge = Table.AddColumn(Source, "Colours", each Text.Combine({[foreground], [background]}, " "), type text),
    ROC = Table.SelectColumns(Merge,{"Colours"}),
    RemDup = Table.Distinct(ROC)
in
    RemDup[/SIZE]

if you change/add/remove anything just refresh green tables or Ctrl+Alt+F5
 
Last edited:

merlin777

Well-known Member
Joined
Aug 29, 2009
Messages
1,395
Office Version
  1. 2007
exactly what i need - unfortunately I have 2007/2010!
Excel has new functions in Excel365 Insider. They do exactly what you wish and they do it in a jiffy. They are dynamic and they are functions that 'spill'.

ABCDE
1designforegroundbackgroundsizetype
2plainwhiteblackXLT-shirt
3plainwhiteblackMT-shirt
4plainbrownyellowLcap
5paisleyblueyellowLcap
6paisleyblueyellowMT-shirt
7
8
9colour combinations used
10white black
11brown yellow
12blue yellow
13
14
15colour combinations per design
16plainpaisley
17white blackblue yellow
18brown yellow

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet38

Worksheet Formulas
CellFormula
A10=UNIQUE(B2:B6&" "&C2:C6)
A16=TRANSPOSE(UNIQUE(A2:A6))
A17=UNIQUE(FILTER($B$2:$B$6&" "&$C$2:$C$6,$A$2:$A$6=A16))
B17=UNIQUE(FILTER($B$2:$B$6&" "&$C$2:$C$6,$A$2:$A$6=B16))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

<strike>
</strike>
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,421
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

I don't have those functions either - bummer. Meanwhile, until you get them, see if this macro works for you (very lightly tested), using the raw data layout shown below.
Excel Workbook
ABCDEFGHIJK
1designforegroundbackgroundsizetypeAll CombosDesign Combosplainpaisley
2plainwhiteblackXLT-shirtwhite blackwhite blackblue yellow
3plainwhiteblackMT-shirtbrown yellowbrown yellow
4plainbrownyellowLcapblue yellow
5paisleyblueyellowLcap
6paisleyblueyellowMT-shirt
Sheet5


Code:
Sub merlin777_3()
Dim Rin As Range, Vin As Variant, Vout As Variant, d As Object, i As Long, numDesigns As Long, Vdesigns As Variant
Set Rin = Range("A1").CurrentRegion
Vin = Rin.Value
Application.ScreenUpdating = False
Rin.Offset(0, Rin.Columns.Count).ClearContents
Range("G1").Resize(1, 3).Value = Array("All Combos", "", "Design Combos")
Rin.Columns(1).AdvancedFilter Action:=xlFilterCopy, copytorange:=Range("H1"), unique:=True
With Range("H2:H" & Cells(Rows.Count, "H").End(xlUp).Row)
    numDesigns = .Count
    .Copy
End With
Range("J1").PasteSpecial xlPasteValues, , , True
Range("H:H").ClearContents
'Get all combos
Set d = CreateObject("Scripting.dictionary")
For i = 2 To UBound(Vin, 1)
    If Not d.exists(Vin(i, 2) & " " & Vin(i, 3)) Then
        d.Add Vin(i, 2) & " " & Vin(i, 3), d.Count + 1
    End If
Next i
Range("G2:G" & d.Count + 1).Value = Application.Transpose(d.keys)
d.RemoveAll
'Get combos by design
Vdesigns = Range("J1", Cells(1, "J").End(xlToRight)).Value
For i = 1 To UBound(Vdesigns, 2)
    For j = 2 To UBound(Vin, 1)
        If Vin(j, 1) = Vdesigns(1, i) Then
            If Not d.exists(Vin(j, 2) & " " & Vin(j, 3)) Then
                d.Add Vin(j, 2) & " " & Vin(j, 3), d.Count + 1
            End If
        End If
    Next j
    Cells(1, "J").Offset(1, i - 1).Resize(d.Count, 1).Value = Application.Transpose(d.keys)
    d.RemoveAll
Next i
ActiveSheet.UsedRange.EntireColumn.AutoFit
Application.ScreenUpdating = True
End Sub
 

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,379
Office Version
  1. 365
Platform
  1. Windows
The new functions are immensely powerful. And they are coming to a computer near you someday!

Watch MrExcel's videos, like this one and the subsequent ones about some of the new functions, UNIQUE, SORT & FILTER. Powerful.

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]https://www.youtube.com/watch?v=ZmLu0vMRrGs[/FONT]
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,190
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Power Query is available to you in 2010. You will need to download it, however. It is FREE.
 

merlin777

Well-known Member
Joined
Aug 29, 2009
Messages
1,395
Office Version
  1. 2007
If I downloaded and used it would someone opening the workbook on another pc need it installed on theirs too?
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,190
Office Version
  1. 365
Platform
  1. Windows
If I downloaded and used it would someone opening the workbook on another pc need it installed on theirs too?

Not to view the end result. They would need PQ if they were trying to look at the query in PQ and manipulate the data using PQ.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,574
Messages
5,637,162
Members
416,959
Latest member
Mohzein

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
Top