How to pull options associated with a product

[joshbjames]

I am in excel trying to pull a list of options for each product I have. For example -

Column A/Column B
Pants/Red
Pants/Blue
Pants/Green
Shorts/Orange
Shorts/Yellow
Shirt/Striped
Shirt/Polkadots
Shirt/Chevron
Shirt/Floral

What I would like is to be able to use my list of product names in Column A and call the options out to the side like below.

Column A/Column B/Column C/Column D/ Column E
Pants/Red/Blue/Green
Shorts/Orange/Yellow
Shirt/Striped/Polkadots/Chevron/Floral

They could even be comma separated, so I could divide them into columns

Pants,Red,Blue,Green
Shorts,Orange,Yellow
Shirt,Stripped,Polkadots,Chevron,Floral

Any help would be greatly appreciated!

 

[Fluff]

How about

Sub GetOptions()

   Dim Cl As Range
   Dim Itm As Variant
   
   With CreateObject("scripting.dictionary")
      For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
         If Not .exists(Cl.Value) Then
            .Add Cl.Value, Cl.Offset(, 1).Value
         Else
            .Item(Cl.Value) = .Item(Cl.Value) & "|" & Cl.Offset(, 1).Value
         End If
      Next Cl
      Range("H2").Resize(.Count).Value = Application.Transpose(.keys)
      For Each Itm In .items
         Range("I" & Rows.Count).End(xlUp).Offset(1).Resize(, UBound(Split(Itm, "|")) + 1).Value = Split(Itm, "|")
      Next Itm
   End With
End Sub

This will create a list of items & options starting in col H

 

[DRSteele]

This formulaic solution might work for you. You will need to create the unique list of each piece of Apparel as I did for ColumnD. Copy E2 downwards.

	A	B	C	D	E
1	Apparel	Colour		Unique List	Colours
2	Pants	Red		Pants	Red, Blue, Green
3	Pants	Blue		Shorts	Orange, Yellow
4	Pants	Green		Shirt	Striped, Polkadots, Chevron, Floral
5	Shorts	Orange
6	Shorts	Yellow
7	Shirt	Striped
8	Shirt	Polkadots
9	Shirt	Chevron
10	Shirt	Floral

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet56​

Array Formulas Cell Formula E2 {=TEXTJOIN(", ",1,IF(D2=$A$2:$A$10,$B$2:$B$10,""))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[joshbjames]

For some reason, it is returning the value #NAME ?

I've tinked a little, but I have it set up so I could copy your formula exactly and it had the same problem.

 

[Fluff]

What version of Excel are you using?
TextJoin only came in with 2016