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.

[TABLE="width: 558"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]Oranges[/TD]
[TD]Apples[/TD]
[TD]Pears[/TD]
[TD]Bananas[/TD]
[TD]Grapes[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Oranges, Pears[/TD]
[TD]y[/TD]
[TD][/TD]
[TD]y[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Apples, Bananas, Grapes[/TD]
[TD][/TD]
[TD]y[/TD]
[TD][/TD]
[TD]y[/TD]
[TD]y[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Apples, Pears[/TD]
[TD][/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Oranges, Apples, Bananas[/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD][/TD]
[TD]y[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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.

 

[BraveDaveIA]

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?

It is indeed.
Thanks!

(You've inspired me to try to learn a bit of VBA.)