Index & Match, Concatenate multiple results

[joetabush]

I've got a decent background on VLOOKUP & CONCATENATE, but apparently those 2 fall short of exactly what I'm looking to do... (I'm pretty sure I need Index & Match, but I'm not doing a good job of teaching myself...)

Sheet1 contains a table of 'color packs' (table below shows A1:E6. Column A is the Color Code ('201'), and Columns B:E are the colors in each pack... max of 4)

(columns) A B C D E
201 Black White (empty cell) (empty cell)
202 Black Grey (empty cell) (empty cell)
203 White Grey (empty cell) (empty cell)
401 Black Grey Red Blue
402 White Yellow Blue Grey


In Sheet2, I am looking to list all colors in column A, and use a CONCATENATE (or similar) to string together all the packs in which that Color appears.
Example:

Column A Column B
Black 201, 202, 401
White 201, 203, 402
Grey 202, 203, 401, 402
Red 401
Blue 401, 402
Yellow 402


Thank you in advance!!
-Joe

 

[Sam_D_Ben]

Sorry

 

[markmzz]

Maybe this array formula (use Ctrl+Shift+Enter and not only Enter):

=IFERROR(INDEX(Sheet1!$A$2:$A$6,SMALL(IF(Sheet1!$B$2:$E$6=$A2,ROW(Sheet1!$B$2:$B$6)-ROW(Sheet1!$B$2)+1),COLUMNS($B2:B2))),"")

Markmzz

 

[Sam_D_Ben]

WOW @ Mark

 

[joetabush]

Thanks but no good.... Returns only one result, not multiple results...

Maybe this array formula (use Ctrl+Shift+Enter and not only Enter):

=IFERROR(INDEX(Sheet1!$A$2:$A$6,SMALL(IF(Sheet1!$B$2:$E$6=$A2,ROW(Sheet1!$B$2:$B$6)-ROW(Sheet1!$B$2)+1),COLUMNS($B2:B2))),"")

Markmzz

 

[markmzz]

Thanks but no good.... Returns only one result, not multiple results...

Here is the result:

A B C D E
Color Cod01 Cod02 Cod03 Cod04
Black 201 202 401
White 201 203 402
Grey 202 203 401 402
Red 401
Blue 401 402
Yellow 402

Did you press Ctrl+Shift+Enter?

Markmzz

 

[Sam_D_Ben]

As Markzz mentioned,

Use curl brackets,

Excel 2007
	A	B	C	D	E
1	A	B	C	D	E
2	Black	201	202	401	201,202,401
Sheet2

Cell Formulas
Range	Formula
E2	=B2&","&C2&","&D2
B2	{=IFERROR(INDEX(Sheet1!$A$2:$A$6,SMALL(IF(Sheet1!$B$2:$E$6=$A2,ROW(Sheet1!$B$2:$B$6)-ROW(Sheet1!$B$2)+1),COLUMNS($B2:B2))),"-")}
C2	{=IFERROR(INDEX(Sheet1!$A$2:$A$6,SMALL(IF(Sheet1!$B$2:$E$6=$A2,ROW(Sheet1!$B$2:$B$6)-ROW(Sheet1!$B$2)+1),COLUMNS($B2:C2))),"-")}
D2	{=IFERROR(INDEX(Sheet1!$A$2:$A$6,SMALL(IF(Sheet1!$B$2:$E$6=$A2,ROW(Sheet1!$B$2:$B$6)-ROW(Sheet1!$B$2)+1),COLUMNS($B2:D2))),"-")}
Press CTRL+SHIFT+ENTER to enter array formulas.

Muz.

 

[markmzz]

As Markzz mentioned,

Use curl brackets,

Muz.

Thanks Muzama Christo.


Markmzz

 

[Aladin Akyurek]

Sheet1, A:E, houses the data...

[TABLE="width: 240"]
<colgroup><col style="width: 48pt;" span="5" width="64"> <tbody>[TR]
[TD="class: xl63, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]201[/TD]
[TD="class: xl63, bgcolor: transparent"]Black[/TD]
[TD="class: xl63, bgcolor: transparent"]White[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]202[/TD]
[TD="class: xl63, bgcolor: transparent"]Black[/TD]
[TD="class: xl63, bgcolor: transparent"]Grey[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]203[/TD]
[TD="class: xl63, bgcolor: transparent"]White[/TD]
[TD="class: xl63, bgcolor: transparent"]Grey[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]401[/TD]
[TD="class: xl63, bgcolor: transparent"]Black[/TD]
[TD="class: xl63, bgcolor: transparent"]Grey[/TD]
[TD="class: xl63, bgcolor: transparent"]Red[/TD]
[TD="class: xl63, bgcolor: transparent"]Blue[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]402[/TD]
[TD="class: xl63, bgcolor: transparent"]White[/TD]
[TD="class: xl63, bgcolor: transparent"]Yellow[/TD]
[TD="class: xl63, bgcolor: transparent"]Blue[/TD]
[TD="class: xl63, bgcolor: transparent"]Grey[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[/TR]
</tbody>[/TABLE]

Sheet2, A:B, houses the processing...

[TABLE="width: 201"]
<colgroup><col style="width: 48pt;" width="64"> <col style="width: 153pt; mso-width-source: userset; mso-width-alt: 7253;" width="204"> <tbody>[TR]
[TD="class: xl65, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl65, width: 204, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Black[/TD]
[TD="class: xl65, bgcolor: transparent"]201, 202, 401[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]White[/TD]
[TD="class: xl65, bgcolor: transparent"]201, 203, 402[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Grey[/TD]
[TD="class: xl65, bgcolor: transparent"]202, 203, 401, 402[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Red[/TD]
[TD="class: xl65, bgcolor: transparent"]401[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Blue[/TD]
[TD="class: xl65, bgcolor: transparent"]401, 402[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Yellow[/TD]
[TD="class: xl65, bgcolor: transparent"]402[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
</tbody>[/TABLE]

First, add the following code as a module to your workbook using Alt+F11...

Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
Dim y As Variant
If TypeOf a Is Range Then
For Each y In a.Cells
aconcat = aconcat & y.Value & sep
Next y
ElseIf IsArray(a) Then
For Each y In a
aconcat = aconcat & y & sep
Next y
Else
aconcat = aconcat & a & sep
End If
aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function

Now invoke on Sheet2...

B2, control+shift+enter, not just enter, and copy down:

=REPLACE(aconcat(IF(FREQUENCY(IF(Sheet1!$A$2:$A$6<>"",
  IF(Sheet1!$B$2:$E$6=$A2,MATCH(Sheet1!$A$2:$A$6,Sheet1!$A$2:$A$6,0))),
  ROW(Sheet1!$A$2:$A$6)-ROW(Sheet1!$A$2)+1),", "&Sheet1!$A$2:$A$6,"")),1,2,"")

 

[markmzz]

Column A Column B
Black 201, 202, 401
White 201, 203, 402
Grey 202, 203, 401, 402
Red 401
Blue 401, 402
Yellow 402


Thank you in advance!!
-Joe

Sorry,

I didn't notice the commas (thanks Aladin).

Try this array formula (use Ctrl+Shift+Enter and not only Enter):

=IFERROR(INDEX(Sheet1!$A$2:$A$6,SMALL(IF(Sheet1!$B$2:$E$6=$A2,ROW(Sheet1!$B$2:$B$6)-ROW(Sheet1!$B$2)+1),1)),"")&
IFERROR(", "&INDEX(Sheet1!$A$2:$A$6,SMALL(IF(Sheet1!$B$2:$E$6=$A2,ROW(Sheet1!$B$2:$B$6)-ROW(Sheet1!$B$2)+1),2)),"")&
IFERROR(", "&INDEX(Sheet1!$A$2:$A$6,SMALL(IF(Sheet1!$B$2:$E$6=$A2,ROW(Sheet1!$B$2:$B$6)-ROW(Sheet1!$B$2)+1),3)),"")&
IFERROR(", "&INDEX(Sheet1!$A$2:$A$6,SMALL(IF(Sheet1!$B$2:$E$6=$A2,ROW(Sheet1!$B$2:$B$6)-ROW(Sheet1!$B$2)+1),4)),"")

Now, the results:

Color	Codes
Black	201, 202, 401
White	201, 203, 402
Grey	202, 203, 401, 402
Red	401
Blue	401, 402
Yellow	402

Markmzz