Search code examples
regexgoogle-sheetsfiltergoogle-sheets-formulags-conditional-formatting

Conditional Formating based on Another Column and If value from the 1st column is not repeated

I have 2 columns in a Google Sheet document.

Column A is GUID's and the column B are Restaurants names.

Example:

Column A                                | Column B
{00000000-0000-0000-0000-000000000001}  | Bistro A
{00000000-0000-0000-0000-000000000001}  | Bistro A
{00000000-0000-0000-0000-000000000001}  | Bistro A
{00000000-0000-0000-0000-000000000002}  | Bistro B
{00000000-0000-0000-0000-000000000002}  | Bistro B
{00000000-0000-0000-0000-000000000003}  | Bistro C
{00000000-0000-0000-0000-000000000003}  | Bistro C
{00000000-0000-0000-0000-000000000004}  | Bistro D
{00000000-0000-0000-0000-000000000004}  | Bistro D
{00000000-0000-0000-0000-000000000004}  | Bistro E

Now, I have Column B ordered from A to Z and I have a Conditional Formatting in Column A and B that put's the Cell Green when the name is repeated.

My problem is: I would like a way or a Conditional Formatting that could check if the value from Column A, if it's repeated when isn't a match with column B, could show Red for example.

I'm trying to see if I'm not having the same GUID with different names. Much like the last example row, where the GUID is the same for "Bistro D" and "Bistro E".

Solution

  • use this custom formula:

    =REGEXMATCH(A1,TEXTJOIN("|",1,FILTER(A:A,COUNTIF(A:A,A:A)<>COUNTIF(B:B,B:B))))

    0