CODEWITHSUNDEEP
regexgoogle-sheetscountgoogle-sheets-formulaconditional-formatting
GMurdock
GMurdock

Reputation: 33

Highlight duplicates when part of the cell matches in Google Sheets

I have searched as much as I can, and I have found solutions for similar problems, but I haven't been able to find a solution to my exact problem.

Issue: I would like to highlight the row when one cell in column A of that row is an exact match for another cell in that column, AND part of another cell in column B of that row is a match for part of another cell in that column, in Google Sheets. I would like to use conditional formatting, and only highlight the second occurence and on.

For example, is this "sheet":

  A          B             C
1|John Smith|[email protected]|Test Co.
2|Jane Doe  |[email protected]   |X Company
3|John Smith|[email protected] |Test Inc.
4|John Smith|[email protected] |Test Incorporated

I would like row 3 and row 4 to highlight, because column A3 is a duplicate of A1, and everything in B3 after @ matches everything in B1 after @, and the same is true of row 4. Also, only rows 3 and 4 should highlight; not row 1, since it is the first instance. I understand regexes, and I've found how to highlight a row if one cell in column A and one cell in column B is an exact match with other cells is their respective columns, but I haven't figured out how to combine the two where I can search for one cell that is an exact match with another cell in that column AND for one cell that is a partial match with another cell in that particular column. Here is a link to a test sheet that contains the sample info from above. https://docs.google.com/spreadsheets/d/1neZd213C1ssY7bPeBfu2xI3WPCmt-oKkfbdrXrid9I8/edit?usp=sharing

Upvotes: 1

Views: 1183

Answers (3)

Andrew Apell
Andrew Apell

Reputation: 23

I created an add-on called Flookup that can handle this task and more.

To highlight duplicates using Flookup, follow these steps [assuming you have already installed Flookup]:

  1. Head to Extensions > Flookup > Highlight duplicates.
  2. Click the menu item labelled "By percentage".
  3. Select a range with one or more columns. This specifies the number of columns, on the row with duplicates, that you want to highlight. In this case, we shall select range A2:C4.
  4. Select "Highlight all duplicates" from the drop-down menu.
  5. Click "Map columns in selection" in order to map the columns in your selection.
  6. Enter a value of "1" as the "Index One" value. This allows us to analyse the first column of your selection for duplicates. If you do not input anything, then the first column of the selected range will be automatically analysed.
  7. Specify the level of similarity you want to use to identify duplicates using the "Threshold" parameter. If you do not input anything, then only exact matches will be highlighted.
  8. Click "Highlight duplicates".

The rows that contain the duplicates will be highlighted in "yellow" after this step.

Please note that you could choose to highlight duplicates by sound similarity as well, especially if you are processing names.

To learn more about this feature, please visit this documentation page.

Upvotes: 1

z..
z..

Reputation: 12873

Try the following custom formula applied to A1:C:

=index((countif($A$1:$A1,$A1)>1)*
(countif(regexextract($B$1:$B1,"@(.*)"),
regexextract($B1,"@(.*)"))>1))

Upvotes: 1

player0
player0

Reputation: 1

use:

=INDEX(COUNTIFS($A:$A&REGEXEXTRACT($B:$B, "@.+"), $A1&REGEXEXTRACT($B1, "@.+"), 
 ROW($A:$A), "<="&ROW($A1))>1)*(A:A<>"")

enter image description here

Upvotes: 2

Related Questions