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

 

[jamilm]

this is very cool. amazing udf and amazing formula.

thanks Aladin.

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

[TABLE="width: 240"]
<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"]
<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,"")

 

[Yodelayheewho]

The following formula works great. However, I have more 'codes' than the four shown in this example. I have as many as 2,000. Is there a way to make the formula shorter and still work?

=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)),"")

 

[Aladin Akyurek]

The following formula works great. However, I have more 'codes' than the four shown in this example. I have as many as 2,000. Is there a way to make the formula shorter and still work?

=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)),"")

Do you have the TEXTJOIN function on your system? If not, is it possible for you to use a vba function like ACONCAT which is shown in this very thread?