Google-sheets – Match mixed data

google sheets

In one column there are mixed sections, which are to be matched with data from other sheet. But as the sections format is different in entire column, not able to match

I have 2 sheets in which i have

Sheet 1 :

Column A         
AB-C,DE

Sheet 2 :

Column A    
ABCDE

I have to match this data in both the sheets

Best Answer

So my solution isn't perfect du to my limited knowledge in regular expression combined to the messy data.

First, extract the codes from Sheet1!A:A, replacing non digits with an emoji.

REGEXREPLACE(A2,"[^\d*]","🕳️")

Then split each code to create an array

split(REGEXREPLACE(A2,"[^\d*]","🕳️"),"🕳️",true,true)

Now we need to create a regex when we'll try to matches theses codes with Sheet2

"^"&textjoin("|-",true,split(REGEXREPLACE(A2,"[^\d*]","🕳️"),"🕳️",true,true))&"-"

Now use this regex with regexmatch to find correct matches. This formula returns the row if true.

=ArayFormula(if(REGEXMATCH(Sheet2!A3:A,"^"&textjoin("|-",true,split(REGEXREPLACE(A3,"[^\d*]","🕳️"),"🕳️",true,true))&"-"),row(Sheet2!A3:A),))

Finally =textjoin to display results in one cell :

=TEXTJOIN("|",true,
   ArrayFormula(
      if(
            REGEXMATCH(Sheet2!A2:A,"^"&textjoin("|-",true,split(REGEXREPLACE(A2,"[^\d*]","🕳️"),"🕳️",true,true))&"-")
           ,row(Sheet2!A2:A),)
   )
)

Illustration :

Related Solutions

Google Sheets – Partial Match for Strings Truncated Beyond 30 Characters

You want to test the equality of the first 30 characters of each string. The command left can be used for this:

=arrayformula(if(iserror(vlookup(left(N:N, 30), left(Format!B:B,30), 1, False)), "that", "this"))

The formula returns "this" if there is a match in column B, and "that" otherwise.

Google Sheets Conditional Formatting – Highlight Data Matching Separate Table

EXPLANATION OF BAD FORMULA

=MATCH(E6,indirect("'5 Star Gear Sets (Hidden)'!E6:E"),0) =MATCH(B6,indirect("'5 Star Gear Sets (Hidden)'!A6:A"),0)

The extra formula starting with a second = is the same as wrapping the entire formula with AND( , ).

=AND(MATCH(E6,indirect("'5 Star Gear Sets (Hidden)'!E6:E"),0),MATCH(B6,indirect("'5 Star Gear Sets (Hidden)'!A6:A"),0)

This Formula does not work because it checks to see if E6 matches any cell in E6 thru E of the referenced sheet and then also checks if B6 matches any cell in A6 thru A of the referenced sheet. Once it finds the first match in the reference sheet, it does NOT use the same row in the range of the second MATCH function.

=MATCH(search_key, range, [search_type])

Example: =MATCH(A6,B1:B100,0)

Using MATCH in a Conditional Format will return True or False.

DISCLAIMER

I wrote the answer for a different sheet than the one listed in the original question. If using link provided in the question for the spreadsheet, the answer is meant to be applied to what would normally be considered Sheet1.

ANSWER

Apply to Range: E6:E

Format Cell if...: Custom Formula

Formula: =AND(NOT(FILTER(INDIRECT("'6 Star Gear Sets (Hidden)'!D$6:D"),INDIRECT("'6 Star Gear Sets (Hidden)'!A$6:A")=B6)=E6),E6<>"")

Formating Colors Text Color: None

Background Color: Light Red

=FILTER(range, condition1, [condition2], ...)

Example: =FILTER(A1:A100,B1:B100=C1)

The FILTER( , ) function needs to be used instead of the MATCH( , , ) function. FILTER( , ) references the cell(s) from the range whos row(s) matches the condition(s).

=INDIRECT(cell_reference_as_string, [is_A1_notation])

Example: =INDIRECT("Sheet2!A1:B")

The INDIRECT( ) function has to be used because the reference sheet can not be referenced directly.

=INDIRECT("'6 Star Gear Sets (Hidden)'!A6:A")=B6

=B6 has to be added to condition1 so that only the value in the range are referenced whos row matches the condition1. This finds which cell in range A6 thru A in the reference sheet matches B6. Only 1 cell is returned because A6 thru A in the reference sheet are unique.

=INDIRECT("'6 Star Gear Sets (Hidden)'!A$6:A")=B6

$ needs to be added in front of the row in both the range and condition in order for them NOT to change as the Conditional Format Rule goes down each cell to apply the condition to. (Something I failed to do in the original formula.)

=FILTER(INDIRECT("'6 Star Gear Sets (Hidden)'!D$6:D"),INDIRECT("'6 Star Gear Sets (Hidden)'!A$6:A")=B6)

Add the FILTER( , ) function and the range. '6 Star Gear Sets (Hidden)'!D$6:D returns the contents of D6 thru D of the referenced sheet that is on the same row as 6 Star Gear Sets (Hidden)'!A$6:A found in condition1.

=FILTER(INDIRECT("'6 Star Gear Sets (Hidden)'!D$6:D"),INDIRECT("'6 Star Gear Sets (Hidden)'!A$6:A")=B6)=E6

=E6 has to be added to the end of FILTER( , ) so that the formula checks to see if E6 matches the resulting contents of the cell found from D6 thru D of the referenced sheet. Because it is a Conditional Formating Rule, the result of the FILTER is either True or False, thus applying the Format if it is True and NOT applying the Format if it is False.

=NOT(FILTER(INDIRECT("'6 Star Gear Sets (Hidden)'!D$6:D"),INDIRECT("'6 Star Gear Sets (Hidden)'!A$6:A")=B6)=E6)

=NOT is added because we want True to actually be False and False to actually be True. We are trying to Format the cells that are not True of the FILTER( , ) we are using.

=AND(NOT(FILTER(INDIRECT("'6 Star Gear Sets (Hidden)'!D$6:D"),INDIRECT("'6 Star Gear Sets (Hidden)'!A$6:A")=B6)=E6),E6<>"")

=AND( , ) and E6<>"" has to be added so that it does NOT format every cell in column E that is blank. =AND( , ) in this case states that not only does the FILTER( , ) must be true, but also E6<>"". E6<>"" just states that E6 is not BLANK.

Best Answer

Related Solutions

Google Sheets – Partial Match for Strings Truncated Beyond 30 Characters

Google Sheets Conditional Formatting – Highlight Data Matching Separate Table

Related Topic