Highlight Partial Matching Duplicates in Google Sheets

Published on

Highlighting generally means conditional formatting in Google Sheets. In this Google Sheets tutorial, I am trying to explain how to conditionally format or highlight partial matching duplicates.

I am sharing a few formulas for your use in the custom formula rule in conditional formatting. Yes! There are three different types of formulas that I am sharing with you to highlight the partial match in different ways in Google Sheets.

Matching duplicates and partial matching duplicates are two different terms. For example, consider the drawings # AB-DRG-15487-1 and AB-DRG-15487-1 Rev 1. Both are different in terms of the normal duplicate definition. But in a partial match, if you want, you can consider both are the same.

AB-DRG-15487-1

AB-DRG-15487-1 Rev 1

See the similarities. Both are the same drawings but the second one is a revision of the first. So if you want to highlight both the drawings considering as one, then you have to follow the partial matching duplicate approach. Else if you want to only consider the exact match, then just follow my earlier tutorial – Highlight Duplicates in Single, Multiple Columns, All Cells in Google Sheets.

Here I am explaining how to highlight partial matching duplicates in Google Sheets. The function that we can use for this purpose is Countif.

To learn Countif, as well as all the other Count functions in detail, see my guide How to Use All Google Sheets Count Functions (All 8 Count Functions).

Conditional Format/Highlight Partial Matching Duplicates in Google Sheets

The formula is different for a single column partial match and across columns or you can say entire cells in a sheet.

If you want to highlight partial matching duplicates in a single column, you can use the below formula.

Conditional Format Partial Matching Duplicates in Single Column in Google Doc Sheets

Formula:

=if(A1<>"",Countif(A$1:A,left(A1,7)& "*") > 1)

This formula will partially match and highlight the duplicates in Column A1: A.

If you wish to conditionally format partial duplicates in any other column, change the cell reference accordingly.

understand what is partial match highlight

Further, this formula matches the first 7 characters only. To match the first ‘n’ characters, change the number of characters in the Left formula as below.

left(A1,n)

In this replace ‘n’ with the number of characters that you want to match.

Conditional format rule for Partial Match

Highlight Partial Matching Duplicates Across Sheets – Single Column

This is a rare scenario. I have two Sheets in my Google Sheets file named as Sheet1 and Sheet2.

This time I want to conditionally format cells in column A in Sheet if it has partial matching duplicates in column A in same sheet or Column A in Sheet2.

This is called partial duplicates across sheet tabs.

Formula:

=if(A1<>"",(countif($A$1:$A,left(A1,7)& "*")+countif(indirect("Sheet2!$A$1:$A"),left(A1,7)& "*"))>1)

This formula is for Sheet1. In this, the first Countif Formula is the one that I have used in the earlier example.

Since a second sheet has been involved, it’s a must to use the Indirect function in Conditional Formatting.

Must Read: Role of Indirect Function in Conditional Formatting in Google Sheets.

Other than this there is not much difference in the second Countif. This is again a partial match. To highlight exact match across sheets, you can check my tutorial – How to Conditional Format Duplicates Across Sheet Tabs in Google Sheets.

Highlight Partial Matching Duplicates Across Cells in Google Spreadsheets

When you want to partial match duplicates across the cells in one sheet, then you should change the formula as below. The below formula is for the range A1: E.

=if(A1<>"",Countif($A$1:E,left(A1,7)& "*") > 1)

Change the formula as needed I mean enter your actual cell references in this formula.

Conditional format Partial Matching Duplicates in Google Sheets

To apply this partial match conditional formatting rule, follow the below steps.

Go to the menu Format > Conditional formatting.

Set Apply to range as A1: E or as you require. In the custom formula field, enter the just above formula.

Here again, you can go for across sheet tab approach. If you have carefully studied my above examples, you can do that yourself.

Thanks for the stay, Enjoy!

Related Reading:

1. Data Validation – How Not to Allow Duplicates in Google Sheets.

2. How to Filter Duplicates in Google Sheets and Delete.

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Lookup Values Under Categories in Excel and Google Sheets

We can use a combination of XLOOKUP and VLOOKUP in both Excel and Google...

Extract Items Under a Category from the Same Column in Excel

In Excel, you can use a combination of the XLOOKUP and DROP functions to...

How to Create a Searchable Table in Excel Using the FILTER Function

Finding specific records, or rows containing the required information, is straightforward in Excel using...

Time Sequences in Excel by Minute, Hour, or Second Increments

Creating time sequences, whether by hour, minute, or second increments in Excel, can be...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

11 COMMENTS

  1. Hi,

    Is there a way to highlight cells with ANY 5 matching letters, regardless of where they are in the cell, and ignoring LEFT or RIGHT?

    • Hi, Moe,

      Try this rule.

      =counta(unique(split(regexreplace(regexreplace(C2,"[^abcpq]",""),".", "$0,"),","),1))=5

      Replace C2 with the corresponding cell reference and “abcpq” with your choice of letters.

  2. Hi,

    Is there a way to highlight different address variations?

    Ex.
    335 S Los robles ave
    335 south Los robles ave

    2603 RAE DELL AVE APT 1
    2603 RAE DELL AVE # 1

    57 Westland ave apt A
    57 Westland Ave Unit 1

  3. Hi, I am trying to adapt this function to the right side of a string of numbers in google docs.

    But when I change the function to RIGHT instead of LEFT, it does nothing. Any suggestions?

  4. Hi!

    I know, in Excel, you can do this.

    But, can Google Sheet apply a conditional format to a partial text in a cell? NOT to the entire cell, only a portion of the text inside the cell (the one that matches the rule).

    Thanks!

  5. I have one column with date, next with event names, last is with names.

    How to highlight duplicate names (last column) by 1st and 2nd columns using conditional formatting in google sheets?

  6. Is there a way to match the contents of the cell for all but the last four characters of one? (e.g., “Image D53” and “Image D53.png”?

    • Hi, Melinda Campbell,

      It’s possible! In my example below, the value “Image D53.png” is in cell G6. Let’s see how to match as per your requirement of leaving the last 4 characters from this value.

      =match("Image D53",left(G6,len(G6)-4))>0

      The LEFT and LEN combination returns the value trimming 4 characters from the end of the string in cell G6.

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.