COLLECT contains ?

Stanislao Bianchini

Hello, I have 2 sheets. In sheet 1 I have column 1A where each cell contains multiple alphanumeric codes separated by a semicolon

(column multiple codes). In sheet 2 I have column 2A where each cell contains one alphanumeric code (column single code) and column 2B where each cell contains the code description (column code description).

I am looking for a formula to be put in sheet 1 that collects all the descriptions from column 2B if one of the alphanumeric codes in the cell in column 1A is present in column 2A. Any idea on how to do this?

thanks

Paul Newcome

Try this...

Insert a helper column (multi-select dropdown) with the following:

=SUBSTITUTE([Column 1A]@row, ";", CHAR(10))

Then in Column 1B you can use this:

=JOIN(COLLECT({Sheet 2 Column 2B}, {Sheet 2 Column 2A}, HAS([Helper Column]@row, @cell)), "///")

Paul Newcome

Try this...

Insert a helper column (multi-select dropdown) with the following:

=SUBSTITUTE([Column 1A]@row, ";", CHAR(10))

Then in Column 1B you can use this:

=JOIN(COLLECT({Sheet 2 Column 2B}, {Sheet 2 Column 2A}, HAS([Helper Column]@row, @cell)), "///")

Stanislao Bianchini

Thanks a lot Paul!

This is great, it works like a charm!

Paul Newcome

Happy to help. 👍️