0

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:

Colour coded dummy table showing what I'm after

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?

1
  • 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.
    – Erik Tyler
    Feb 25, 2022 at 15:29

1 Answer 1

1

See my comment to your original post. That said, and without testing it, you can try this. First, delete everything from Col H (including the header). Then place the following formula in cell H1:

=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"))})

This should produce the header and all results for all rows.

The plain-English translation:

"Create a header with the text 'Duplicate?' Under that, if there is nothing in any cell of Col B, Col D and Col F, leave the corresponding cell blank. Otherwise, count exact matches considering the data in Col B, Col D and Col F together up to the current row. If that number is 1 (in other words, if it is the first occurrence, leave the corresponding cell blank; if the count is anything other than 1, mark the corresponding cell 'Duplicate.'"

2
  • You are a LEGEND Sir! Thank you so much, that's exactly what I needed! Now I will reverse-engineer it so I can understand what you've done and then not have to ask such a question again in the future! Thanks buddy, that was truly very helpful! :D
    – RoCrow
    Feb 26, 2022 at 16:49
  • You're welcome. See my additional note in my post (a plain-English interpretation of the formula, which you may find helpful as you study it).
    – Erik Tyler
    Feb 26, 2022 at 16:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.