extract a list of colour combinations from a table

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:

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

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
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)

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:
Upvote 0
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>
 
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?

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

Forum statistics

Threads
1,213,536
Messages
6,114,213
Members
448,554
Latest member
Gleisner2

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