Thread: Concatenation of Text in Arrays with reference cells

Hi,

Please help with the below.

I want to concatenate the Values in an array based on a reference cell.
For example if Column A and B have the following values.. I want to concatenate the vlaues of Array B for A1 in cell C1.

In other words from the below list I want to concatenate all the matching fruits in column B tagged to each digit in Column A and this concatenated value to be available in Column C for each reference cell.

For easy reference I have attached a sample file. XLSX


A B C(Desired Result)
1 Apple Apple,Orange,Pine
1 Apple Apple
3 Orange Orange,Peach
4 Pine Pine,Peach
5 Peach Peach
6 Peach Peach
2 Pine Pine
3 Peach Orange,Peach
4 Peach Pine,Peach
1 Orange Pine,Peach
1 Pine Pine,Peach

I think your example spreadsheet is wrong, or maybe you could explain

This bit is fine. C2 contains Apple Orange Pine since A2 contains 1 and B2 is Apple, A11 also contains 1 and B11 is Orange and A12 contains 1 and B12 is Pine. So your concatenating the fruits where there is a 1 in column A (and presumably deduplicating else C3 would contain Apple Apple Orange Pine).

Since A11 contains 1 shouldn't C11 also contain Apple Orange Pine?
Same goes for C12 shouldnt that be Apple Orange Pine as well?
Also C3 Apple Orange Pine?

here is one way