Match any part of multi select to another multi select on another sheet

Gianni M

Hello,

I have two separate sheets that each have multi select columns and I want to join a particular column if it has ANY mutual matches between the two.

For example, on sheet 1, A B C are selected in the multi select and I want to find any matches on sheet 2 which contain anything selected from sheet 1. The column on sheet 2 is also a multi select column and can have any combination of these selected.

So if A, B, C are selected on sheet 1 and B, D, E are selected on sheet 2, this should trigger a match.

Paul Newcome

Ok. You are going to want to add 3 columns to the destination sheet (these can be hidden after setup to keep the sheet looking clean). These 3 columns will parse out the multi-select. For these columns I just titled them "A", "B", and "C".

A: =LEFT([Example 1 (Sheet 1)]@row, FIND(CHAR(10), [Example 1 (Sheet 1)]@row) - 1)

B: =LEFT(SUBSTITUTE([Example 1 (Sheet 1)]@row, A@row + CHAR(10), ""), FIND(CHAR(10), SUBSTITUTE([Example 1 (Sheet 1)]@row, A@row + CHAR(10), "")) - 1)

C: =RIGHT([Example 1 (Sheet 1)]@row, LEN([Example 1 (Sheet 1)]@row) - FIND(CHAR(10), [Example 1 (Sheet 1)]@row, FIND(CHAR(10), [Example 1 (Sheet 1)]@row) + 1))

Then in the output column your formula would look like this...

=JOIN(COLLECT([Data 1 (Sheet 2)]:[Data 1 (Sheet 2)], [Example 2 (Sheet 2)]:[Example 2 (Sheet 2)], OR(CONTAINS(A@row, @cell), CONTAINS(B@row, @cell), CONTAINS(C@row, @cell))), ", ")

You would just need to update the appropriate ranges with the cross sheet references.

Paul Newcome

I'm not sure I see where the JOIN comes into play. Are you able to provide screenshots with manually entered mock data that shows what exactly you are trying to accomplish?

Gianni M

Thank you for your response. I have attached a screenshot below and hopefully this explains it a little better. On the screenshot below, let's say that the Example 1 is on its own sheet. What I want to do on sheet 1 is collect and join any data in the Data 1 column if any part of the multi select in Example 1 matches any part of the multi select in Example 2. Since they both contain C, the text from the Data 1 should be collected and joined into the formula on Sheet 1.

Paul Newcome

What exactly would the output be though when you say "Collect and join"?

Gianni M

Here is a new screenshot. The x column is the break between Sheet 1 and Sheet 2.

On Sheet 1, for each row there will be data selected in the Example 1 multi select column. I want the output column to collect and join any data on the Data 1 column in Sheet 2 where any part of Example 1 matches any part of Sheet 2.

Since C matches both rows in Example 2, the expected output on the Output column would be String 1, String 2.

If Example 1 only contained one selection then this would be really simple since I could just do a JOIN(COLLECT()) to match Example 1 to Example 2 but the issue I am facing is that I am trying to match ANY selection in Example 1 to Example 2 to collect the data in the Data 1 column.

Paul Newcome

How many possible selections could be made in a single cell?

Gianni M

Up to 3.

Paul Newcome

Ok. You are going to want to add 3 columns to the destination sheet (these can be hidden after setup to keep the sheet looking clean). These 3 columns will parse out the multi-select. For these columns I just titled them "A", "B", and "C".

A: =LEFT([Example 1 (Sheet 1)]@row, FIND(CHAR(10), [Example 1 (Sheet 1)]@row) - 1)

B: =LEFT(SUBSTITUTE([Example 1 (Sheet 1)]@row, A@row + CHAR(10), ""), FIND(CHAR(10), SUBSTITUTE([Example 1 (Sheet 1)]@row, A@row + CHAR(10), "")) - 1)

C: =RIGHT([Example 1 (Sheet 1)]@row, LEN([Example 1 (Sheet 1)]@row) - FIND(CHAR(10), [Example 1 (Sheet 1)]@row, FIND(CHAR(10), [Example 1 (Sheet 1)]@row) + 1))

Then in the output column your formula would look like this...

=JOIN(COLLECT([Data 1 (Sheet 2)]:[Data 1 (Sheet 2)], [Example 2 (Sheet 2)]:[Example 2 (Sheet 2)], OR(CONTAINS(A@row, @cell), CONTAINS(B@row, @cell), CONTAINS(C@row, @cell))), ", ")

You would just need to update the appropriate ranges with the cross sheet references.

Gianni M

Thank you Paul, this works perfectly and I appreciate the help!

Andrew Davidson

@Paul Newcome I've poured over several threads about JOIN() formulas you've helped others with, attempting to parse values from a multi-selection cell [Supply SKU]. I want to use those values to lookup another sheet to bring in a cost. The abovementioned A,B,C formulas in this thread are the first to get the result I'm looking for however, I have up to 14 values that could be selected. Here are some examples of what I'm working with. Since I have so many values that could be returned, I feel I need to go back to the JOIN() (maybe JOIN(COLLECT())?) formula but can't seem to get it working past the 2nd value. I added in a delimiter "+" to see if that would help but to no avail. Any thoughts are greatly appreciated!