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
Apparel	Colour
Pants	Red	Pants
Pants	Blue	Shorts
Pants	Green	Shirt
Shorts	Orange
Shorts	Yellow
Shirt	Striped
Shirt	Polkadots
Shirt	Chevron
Shirt	Floral

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="bgcolor: #FFF2CC"]Unique List[/TD]
[TD="bgcolor: #FFF2CC"]Colours[/TD]

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

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

[TD="bgcolor: #E2EFDA"]Red, Blue, Green[/TD]

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

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

[TD="bgcolor: #E2EFDA"]Orange, Yellow[/TD]

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

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

[TD="bgcolor: #E2EFDA"]Striped, Polkadots, Chevron, Floral[/TD]

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

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

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

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

[TD="align: center"]7[/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: center"]9[/TD]

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

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

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

</tbody>

Sheet56​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array 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: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2[/TH]
[TD="align: left"]{=TEXTJOIN(", ",1,IF(D2=$A$2:$A$10,$B$2:$B$10,""))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[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