# How to find duplicate entries based on 3 different columns in Google Sheets

I have this dummy sheet with data in Columns A through G. I have colour coded it so I can reference it in my explanation below:

In column H I want to to show the word "Duplicate" whenever there is duplicate data in a Row, but only from Columns B, D & F when combined.

I also want "Duplicate" to only be written on the actual duplicate entry row (i.e. not the first time that data appears in a row, as that is the original, but the second time it appears in a new row).

When there is no duplicate I want the cells in H to remain blank.

Below you can see a colour-coded mock up of what I want to see:

For "Steve" you can see that:

Blue = Rows 2 and 6 have duplicate data in Columns B, D and F combined. Therefore, H6 has the word "Duplicate" in it (and H2 is blank, because it is the original entry)

Green = Rows 4 and 9 both have the same data in D and F columns as rows 2 and 6 above, but the data in the B column is unique (to Steve), therefore it doesn't output a Duplicate result.

For "Fred" you can see that:

Magenta = Rows 3 and 8 contain the same set of data as each other, therefore row 8 had Duplicate in H8, and H3 remains blank.

Yellow = just like in the Steve's example above, rows 5 and 7 don't have duplicate status as cells B5 and B7 have different values.

Also, note that, though cells B3 and B4 have the same value, because their row's values in D3 and D4, and F3 and F4, are different, there is no duplicate.

Therefore, can anyone please show me the correct formula I can use in H to achieve this?

Additionally, the real spreadsheet I'll be applying it to has new rows added daily via an API input. Therefore, ideally, I'd like the above formula to auto-add itself in the H column whenever a new row is added. If that's possible?

• Please share the link to the sample spreadsheet shown in your post. Otherwise, you are expecting the volunteer contributors here to start their own new spreadsheet and type in the data before they can even begin to write or test a formula. Help us help you: share a link. Feb 25, 2022 at 15:29

`=ArrayFormula({"Duplicate?";IF(B2:B&D2:D&F2:F="",,IF(COUNTIFS(B2:B&D2:D&F2:F,B2:B&D2:D&F2:F,ROW(A2:A),"<="&ROW(A2:A))=1,,"Duplicate"))})`