extract a list of colour combinations from a table

[merlin777]

I have a table of which this is an extract:

design	foreground	background	size	type
plain	white	black	XL	T-shirt
plain	white	black	M	T-shirt
plain	brown	yellow	L	cap
paisley	blue	yellow	L	cap
paisley	blue	yellow	M	T-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?

 

[merlin777]

That's brilliant - thanks for this.

Would you be able to do a couple of tweaks for me?

The table is in a different place to the sheet i need the list and it has some extra columns. If this over complicates things ill just link to those table columns on another sheet (as i'm doing now to test the code).

and, after playing around i find i can do everything i need if i have a list of foreground colours and a list of background colours rather than the combination. That means i dont now need to separate by design, just the two lists covering all designs.

Would that be feasible? I can probably get around the first but i cant work out how to get a separate foreground and background list.

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.
Sheet5

	A	B	C	D	E	F	G	H	I	J	K
1	design	foreground	background	size	type		All Combos		Design Combos	plain	paisley
2	plain	white	black	XL	T-shirt		white black			white black	blue yellow
3	plain	white	black	M	T-shirt		brown yellow			brown yellow
4	plain	brown	yellow	L	cap		blue yellow
5	paisley	blue	yellow	L	cap
6	paisley	blue	yellow	M	T-shirt

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:51px;"><col style="width:77px;"><col style="width:79px;"><col style="width:31px;"><col style="width:47px;"><col style="width:28px;"><col style="width:93px;"><col style="width:25px;"><col style="width:103px;"><col style="width:93px;"><col style="width:80px;"></colgroup><tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

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

 

[merlin777]

sorry, should have said the first items in the foreground and background columns in the table are from E310 and F310 on sheet "order list" and the colour lists need to end up on sheet "lists"

 

[JoeMo]

sorry, should have said the first items in the foreground and background columns in the table are from E310 and F310 on sheet "order list" and the colour lists need to end up on sheet "lists"

Don't have a lot of time right now, but if you post your table showing column & row headers in a format that can be copied and pasted to Excel, and include a layout of the sheet you want the list on including column & row headers with some examples of what the output should look like in a format that can be copied and pasted to Excel, I will have a look.

Here's a link to some tools you can use to provide what I am asking for:
https://www.mrexcel.com/forum/about-board/508133-attachments.html