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

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.

Find more posts tagged with

Formulas

Accepted answers

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.

All comments

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

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!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.