extract a list of colour combinations from a table

M

merlin777

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

[TABLE="width: 376"]
<colgroup><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD]design[/TD]
[TD]foreground[/TD]
[TD]background[/TD]
[TD]size[/TD]
[TD]type[/TD]
[/TR]
[TR]
[TD]plain[/TD]
[TD]white[/TD]
[TD]black[/TD]
[TD]XL[/TD]
[TD]T-shirt[/TD]
[/TR]
[TR]
[TD]plain[/TD]
[TD]white[/TD]
[TD]black[/TD]
[TD]M[/TD]
[TD]T-shirt[/TD]
[/TR]
[TR]
[TD]plain[/TD]
[TD]brown[/TD]
[TD]yellow[/TD]
[TD]L[/TD]
[TD]cap[/TD]
[/TR]
[TR]
[TD]paisley[/TD]
[TD]blue[/TD]
[TD]yellow[/TD]
[TD]L[/TD]
[TD]cap[/TD]
[/TR]
[TR]
[TD]paisley[/TD]
[TD]blue[/TD]
[TD]yellow[/TD]
[TD]M[/TD]
[TD]T-shirt[/TD]
[/TR]
</tbody>[/TABLE]


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

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Sort by date Sort by votes
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]
Book1
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
Sheet38
Cell Formulas
RangeFormula
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))
<strike>
</strike>[/FONT]
 
Last edited:
Upvote 0
with PowerQuery (free add-in for Excel 2010 from MS site, 2007 doesn't support PQ)

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]design[/td][td=bgcolor:#5B9BD5]foreground[/td][td=bgcolor:#5B9BD5]background[/td][td=bgcolor:#5B9BD5]size[/td][td=bgcolor:#5B9BD5]type[/td][td][/td][td=bgcolor:#70AD47]design[/td][td=bgcolor:#70AD47]Colours[/td][td][/td][td=bgcolor:#70AD47]Colours[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]plain[/td][td=bgcolor:#DDEBF7]white[/td][td=bgcolor:#DDEBF7]black[/td][td=bgcolor:#DDEBF7]XL[/td][td=bgcolor:#DDEBF7]T-shirt[/td][td][/td][td=bgcolor:#E2EFDA]plain[/td][td=bgcolor:#E2EFDA]white black, brown yellow[/td][td][/td][td=bgcolor:#E2EFDA]white black[/td][/tr]

[tr=bgcolor:#FFFFFF][td]plain[/td][td]white[/td][td]black[/td][td]M[/td][td]T-shirt[/td][td][/td][td]paisley[/td][td]blue yellow[/td][td][/td][td]brown yellow[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]plain[/td][td=bgcolor:#DDEBF7]brown[/td][td=bgcolor:#DDEBF7]yellow[/td][td=bgcolor:#DDEBF7]L[/td][td=bgcolor:#DDEBF7]cap[/td][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]blue yellow[/td][/tr]

[tr=bgcolor:#FFFFFF][td]paisley[/td][td]blue[/td][td]yellow[/td][td]L[/td][td]cap[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]paisley[/td][td=bgcolor:#DDEBF7]blue[/td][td=bgcolor:#DDEBF7]yellow[/td][td=bgcolor:#DDEBF7]M[/td][td=bgcolor:#DDEBF7]T-shirt[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

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:
Upvote 0
exactly what i need - unfortunately I have 2007/2010!
DRSteele said:
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
plainwhiteblackXLT-shirt
plainwhiteblackMT-shirt
plainbrownyellowLcap
paisleyblueyellowLcap
paisleyblueyellowMT-shirt
brown yellow
blue yellow
paisley
brown yellow

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFF2CC"]design[/TD]
[TD="bgcolor: #FFF2CC"]foreground[/TD]
[TD="bgcolor: #FFF2CC"]background[/TD]
[TD="bgcolor: #FFF2CC"]size[/TD]
[TD="bgcolor: #FFF2CC"]type[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #FCE4D6"]colour combinations used[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: #E2EFDA"]white black[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="bgcolor: #FCE4D6"]colour combinations per design[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="bgcolor: #E2EFDA"]plain[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="bgcolor: #C6E0B4"]white black[/TD]
[TD="bgcolor: #C6E0B4"]blue yellow[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet38​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A10[/TH]
[TD="align: left"]=UNIQUE(B2:B6&" "&C2:C6)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A16[/TH]
[TD="align: left"]=TRANSPOSE(UNIQUE(A2:A6))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A17[/TH]
[TD="align: left"]=UNIQUE(FILTER($B$2:$B$6&" "&$C$2:$C$6,$A$2:$A$6=A16))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B17[/TH]
[TD="align: left"]=UNIQUE(FILTER($B$2:$B$6&" "&$C$2:$C$6,$A$2:$A$6=B16))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

<strike>
</strike>
Click to expand...
 
Upvote 0
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
 
Upvote 0
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]
 
Upvote 0
Power Query is available to you in 2010. You will need to download it, however. It is FREE.
 
Upvote 0
If I downloaded and used it would someone opening the workbook on another pc need it installed on theirs too?
 
Upvote 0
If I downloaded and used it would someone opening the workbook on another pc need it installed on theirs too?
Click to expand...

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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

Z
  • Solved
Drop down list using unique but with changed number of rows
Replies
5
Views
345
Fluff
Fluff
F
  • Question Question
Custom search bar - Creating a front end for My Sold Research Tool.
Replies
0
Views
204
frewert
F
GMC The Macro Man
  • Question Question
Copy n Paste Filtered Data
Replies
1
Views
165
GMC The Macro Man
GMC The Macro Man
T
  • Solved
Conditional Formatting of Green vs. Green/White, etc; Diagonal Border Not Possible
2
Replies
11
Views
1K
MARK858
MARK858
Q
  • Solved
Split Column by Delimiter and add new column Name Dynamically
Replies
2
Views
484
QMAN223
Q

Forum statistics

Threads
1,223,888
Messages
6,175,206
Members
452,618
Latest member
Tam84

Share this page

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