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,048
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
3,816
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,666
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,048
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,075
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,075
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,082,246
Messages
5,363,998
Members
400,773
Latest member
jwolfe

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top