Zacariah171
New Member
- Joined
- Apr 2, 2019
- Messages
- 28
I have been able to make a list in column CY that lists any duplicates found in columns D, J, P, and V. Any duplicates found in those columns are listed once in CY. I have the formula written for that listed below and it works fine. Now, I'd like to alter that formula so that it will just list any duplicates that have "PRI" in the column next to it: E, K, Q, and W. I just cant seem to figure out how to add in that extra criteria. In the attached picture, the only item that should be listed in column CY should be Unit 56. Unit 11 is listed twice in column D but only one of those has a Status of PRI next to it, so it's not a duplicate that I'm looking for and should not be listed in CY. Unit 56 is listed twice; once in column D and once in column J and both have a Status of PRI, so that is a duplicate that I'm looking for, so it should be listed once in CY7. Any help is greatly appreciated and please let me know if you need more information.
This is the formula I'm looking to alter to accommodate the extra criteria.
=IFNA(IFERROR(IFERROR(IFERROR(
LOOKUP(2, 1/((COUNTIF($CY$6:CY6, $D$7:$D$37)=0)*(COUNTIF($D$7:$D$37, $D$7:$D$37)>1)), $D$7:$D$37),
LOOKUP(2, 1/((COUNTIF($CY$6:CY6, $J$7:$J$37)=0)*((COUNTIF($J$7:$J$37, $J$7:$J$37)>1)+(COUNTIF($D$7:$D$37, $J$7:$J$37)>0))),$J$7:$J$37)),
LOOKUP(2, 1/((COUNTIF($CY$6:CY6, $P$7:$P$37)=0)*((COUNTIF($P$7:$P$37, $P$7:$P$37)>1)+(COUNTIF($D$7:$D$37, $P$7:$P$37)>0)+(COUNTIF($J$7:$J$37, $P$7:$P$37)>0))), $P$7:$P$37)),
LOOKUP(2, 1/((COUNTIF($CY$6:CY6, $V$7:$V$37)=0)*((COUNTIF($V$7:$V$37, $V$7:$V$37)>1)+(COUNTIF($D$7:$D$37, $V$7:$V$37)>0)+(COUNTIF($J$7:$J$37, $V$7:$V$37)>0)+(COUNTIF($P$7:$P$37, $V$7:$V$37)>0))), $V$7:$V$37)), "")
Thank you.
This is the formula I'm looking to alter to accommodate the extra criteria.
=IFNA(IFERROR(IFERROR(IFERROR(
LOOKUP(2, 1/((COUNTIF($CY$6:CY6, $D$7:$D$37)=0)*(COUNTIF($D$7:$D$37, $D$7:$D$37)>1)), $D$7:$D$37),
LOOKUP(2, 1/((COUNTIF($CY$6:CY6, $J$7:$J$37)=0)*((COUNTIF($J$7:$J$37, $J$7:$J$37)>1)+(COUNTIF($D$7:$D$37, $J$7:$J$37)>0))),$J$7:$J$37)),
LOOKUP(2, 1/((COUNTIF($CY$6:CY6, $P$7:$P$37)=0)*((COUNTIF($P$7:$P$37, $P$7:$P$37)>1)+(COUNTIF($D$7:$D$37, $P$7:$P$37)>0)+(COUNTIF($J$7:$J$37, $P$7:$P$37)>0))), $P$7:$P$37)),
LOOKUP(2, 1/((COUNTIF($CY$6:CY6, $V$7:$V$37)=0)*((COUNTIF($V$7:$V$37, $V$7:$V$37)>1)+(COUNTIF($D$7:$D$37, $V$7:$V$37)>0)+(COUNTIF($J$7:$J$37, $V$7:$V$37)>0)+(COUNTIF($P$7:$P$37, $V$7:$V$37)>0))), $V$7:$V$37)), "")
Thank you.