Need help identifying duplicates based on multiple criteria

[Roxie#2013]

Hi All,

I have an excel problem that is driving me crazy trying to figure it out without doing the work manually. it's a two part problem and luckily I have already done the first part. I have a worksheet where I've got a formula that says wherever you duplicates, identify the first in the series as '1st' and everything else within those duplicate occurrences as 'Other dups'. However, there is a second condition that I can't figure which I need. I need to look within a series of duplicates and look in a specific column (not the duplicate column) and see if at any point within the group of duplicates that column contains a "Y". If it does, then I want to put "yes" in the cell next to the duplicate occurrence column.

Below is an example. As you can see, the 4th row in this series of duplicates contains a 'Y". I want to basically say if within a series of duplicates a "Y" exists, indicate this in cell X. I can't figure out how to tell excel to know what range is each series of duplicates. each group of duplicates could be any number of rows. In other words, the count of duplicate rows per duplicate group is dynamic.


Antioch Delta Fair Medical Offices Medical Office Campus CN55-01 N Dup 1st
Antioch Delta Fair Medical Offices Medical Office Campus CN55-02 N Dup Other Dups
Antioch Delta Fair Medical Offices Medical Office Campus CN55-03 N Dup Other Dups
Antioch Delta Fair Medical Offices Medical Office Campus CN55-04 Y Dup Other Dups
Antioch Delta Fair Medical Offices Medical Office Campus CN55-05 N Dup Other Dups
Antioch Delta Fair Medical Offices Medical Office Campus CN55-51 N Dup Other Dups
Antioch Delta Fair Medical Offices Medical Office Campus CN55-52 N Dup Other Dups

[CAntosh]

Are you able to post a small sample workbook (Use: Go Advanced --> Manage Attachments) so we can see what you're working with? Be sure to alter or remove any sensitive information.

[CAntosh]

If the name is in A1:A8, the Y/N is in C1:C8, and the "1ST/DUPS" is in E1:E8, the formula below, placed in F1 and filled down, should return "YES" next to the first entry with that name if ANY entry with that name has a "Y" in column C.

=IF(E1="1ST",IF(COUNTIFS($A$1:$A$8,$A1,$C$1:$C$8,"Y")>0,"YES",""),"")

That's my best guess at what you're after; hopefully it helps?