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:

[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?
 
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

ABCDEFGHIJK
designforegroundbackgroundsizetype All Combos Design Combosplainpaisley
plainwhiteblackXLT-shirt white black white blackblue yellow
plainwhiteblackMT-shirt brown yellow brown yellow
plainbrownyellowLcap blue yellow
paisleyblueyellowLcap
paisleyblueyellowMT-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>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]

</tbody>


Excel tables to the web >> Excel Jeanie HTML 4

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

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
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"
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,894
Messages
6,175,252
Members
452,623
Latest member
Techenthusiast

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