extract a list of colour combinations from a table

merlin777

Well-known Member
Joined
Aug 29, 2009
Messages
1,388
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?
 

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,074
Office Version
365
Platform
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

Well-known Member
Joined
Oct 24, 2015
Messages
4,159
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,388
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
16,691
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,074
Office Version
365
Platform
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
5,096
Office Version
2019
Platform
Windows
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,388
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
5,096
Office Version
2019
Platform
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.
 

Forum statistics

Threads
1,084,878
Messages
5,380,415
Members
401,673
Latest member
Ali Balleya

Some videos you may like

This Week's Hot Topics

Top