How to add a "Y" to a column cell when other cells with the same site reference show a "Y"

Hi all,

I have a large spreadsheet (~63000 rows) with, in a nutshell, sites and products per site that are scanned. Sites have a reference number and can occur multiple times. Whenever a product is scanned double (by accident) or three times, etc. that site instance should be removed.
I've managed to create a formula where duplicates are shown with a "Y", but I need to find a way to highlight the other "scans" so that I can simply delete the rows (by filtering on the Ys)

Attached sample should hopefully help.

Does anybody have an idea what formula I could use?

Many thanks,
Allard

Hi Pepe,

Thanks for replying, no I'm after a simple way of identifying sites that I'd wish to delete (manually) when any duplicates, triplicated, etc. occur.
Eg. in the attached sample file, in site A12345 Scan 3 is a duplicate so I'd be keen to move this instance of A12345 (not all in the sheet as there might be other A12345s). I've highlighted the duplicates with a "Y", if all other rows (=9 rows) would get a "Y", I could filter on the Ys and delete all rows manually. Hope this makes sense.
Thanks.

Re: How to add a "Y" to a column cell when other cells with the same site reference show a
Does this help entered in C2 ?

=IF(SUMPRODUCT(--($A$2:$A$29&$B$2:$B$29=A2&B2))>1,"y","")

Hi Pepe,

Thanks, this highlights the duplicates, triplicates, etc. with a "y", not unfortunately the other rows in the site.

Regards,
Allard

Will the values in column B in your sheet always be sorted like the way you have shown in your example? If they are, you could use

=IF(AND(B2=B1,A2=A1),"Y","")

Put that formula in C2, and autofill.

This formula will only highlight the duplicates, triplicates, etc. It won't highlight the first instance that a particular entry appears

Not always....thanks for the formula, unfortunately what I'm looking for is a formula that adds a value, such as "y", to the non-duplicate cells within a site that contains duplicate, triplicate, etc. cells. I have been able to identify duplicates etc. through a formula already.
The Site Ref should be the unique identifier in some way.

Thanks.

Thanks TheExcelphile, unfortunately this would only work if the duplicates/triplicates are in sequence, soemtimes there are gaps in between...