Concatenate multiple values from table row into one cell

[BraveDaveIA]

I hope someone can help me with this, seemingly simple(?), need:

I need a formula to search through a table's row for multiple instances of a certain lookup value, then retrieve an associated retrieval value from a different row in that table, and concatenate all those retrieval values into a single cell, delimited by commas.

	A	B	C	D	E	F
1		Oranges	Apples	Pears	Bananas	Grapes
2	Oranges, Pears	y		y
3	Apples, Bananas, Grapes		y		y	y
4	Apples, Pears		y	y
5	Oranges, Apples, Bananas	y	y		y

<tbody>
</tbody>

In the sample table above, the formula would reside in cells A2 through A5. It would be pulling the retrieval values from row 1, based on a "y" appearing in any of the cells B2:F5.

It is not necessary the concatenated retrieval values appear alphabetized.

In reality, my tables will be up to 20 columns and up to 2,000 rows.

Here's hoping there's a way.

 

[FDibbins]

This is a very long-winded way to do this, but it works, so hopefully someone can give you something better...

	A​	B​	C​	D​	E​	F​
1​		Orange	Apple	Pear	Banana	Grape
2​	Orange,Pear	y		y
3​	Apple,Banana,Grape		y		y	y
4​	Apple,Pear		y	y
5​	Orange,Apple,Banana	y	y		y

A2=SUBSTITUTE(TRIM(IF(B2="y",B$1,"")&" "&IF(C2="Y",C$1,"")&" "&IF(D2="y",D$1,"")&" "&IF(E2="Y",E$1,"")&" "&IF(F2="y",F$1,""))," ",",")
copied down

 

[FDibbins]

Here is another (quicker??) way to do this, using a helper table...

	H​	I​	J​	K​	L​	M​
1​		Orange	Apple	Pear	Banana	Grape
2​	Orange,Pear	Orange		Pear
3​	Apple,Banana,Grape		Apple		Banana	Grape
4​	Apple,Pear		Apple	Pear
5​	Orange,Apple,Banana	Orange	Apple		Banana
6​

With your data as shown in my post above,
I2=IF(B2="Y",I$1,"")
copied down and across

Then...
H2=SUBSTITUTE(TRIM(I2&" "&J2&" "&K2&" "&L2&" "&M2)," ",",")
copied down
copied down

 

[BraveDaveIA]

This is a very long-winded way to do this, but it works, so hopefully someone can give you something better...

	A​	B​	C​	D​	E​	F​
1​		Orange	Apple	Pear	Banana	Grape
2​	Orange,Pear	y		y
3​	Apple,Banana,Grape		y		y	y
4​	Apple,Pear		y	y
5​	Orange,Apple,Banana	y	y		y

<tbody>
</tbody>

A2=SUBSTITUTE(TRIM(IF(B2="y",B$1,"")&" "&IF(C2="Y",C$1,"")&" "&IF(D2="y",D$1,"")&" "&IF(E2="Y",E$1,"")&" "&IF(F2="y",F$1,""))," ",",")
copied down

Thanks, FDibbens.

I do have a few dozen tables, each with varying quantities of columns (up to ~20). With this formula, I'll have to add the concatenation for every column in each table. (Then again, I was already prepared for the fact that I'd have to adjust the range notation in each formula to address the varying quantities of columns.)

Thanks again.

 

[Eric W]

If you have the latest version of Excel 2016 with the new functions, you can use this formula in A2:

=TEXTJOIN(", ",TRUE,IF(B2:F2="y",$B$1:$F$1,""))
confirmed with Control+Shift+Enter, and copy down as needed.

If you don't have the latest version, you'll have to do something long-winded like Ford suggests, or use VBA.

 

[ChrisGT7]

You can create your own UDF with VBA. Here is a suggestion:

Function JoinFruits(Rng1 As Range, Rng2 As Range) As String
    Dim R1 As Range
    For Each R1 In Rng1
        If Cells(Rng2.Row, R1.Column) = "y" Or Cells(Rng2.Row, R1.Column) = "Y" Then _
            JoinFruits = JoinFruits & R1.Value & ", "
    Next
    If Right(JoinFruits, 2) = ", " Then JoinFruits = Left(JoinFruits, Len(JoinFruits) - 2)
End Function

Rng1 = the header of the table, Rng2 = the current row

For your example in A2 write: =JoinFruits($B$1:$F$1,B2:F2) and fill down the rest cells of column A.

I hope this will work for the rest of your tables.

 

[BraveDaveIA]

If you have the latest version of Excel 2016 with the new functions, you can use this formula in A2:

=TEXTJOIN(", ",TRUE,IF(B2:F2="y",$B$1:$F$1,""))
confirmed with Control+Shift+Enter, and copy down as needed.

If you don't have the latest version, you'll have to do something long-winded like Ford suggests, or use VBA.

Arrgh! 2010.

 

[BraveDaveIA]

Beauty? That works very well. Thanks.

Now one other slight tweak:
Can the comma+space be made to occur even if there is only one retrieval value? (E.g., "Oranges, ")
I would not want the comma+space to appear if there are no "Y"'s. I.e., no retrieval values apply.

 

[BraveDaveIA]

I meant... Beauty!

 

[ChrisGT7]

If you always want a ", " at the end of the string, then delete the "If Right(JoinFruits, 2) = ", " Then JoinFruits = Left(JoinFruits, Len(JoinFruits) - 2)".

If a row has no Y/y, then the function returns "".

Is that you want?