Find Duplicates with Multiple Conditions

[RattlingCarp3048]

Not really sure what will be the best method for what i am trying to do so i am open to all possibilities (i.e. formula, filter, conditional formatting, VBA, combination of, etc...)

Basically i have 2 main columns where one contains a worklist and the other a case number. The worklists i use the most i have highlighted yellow (there are 13 different ones in total) and the least used are blue (there are 18 different ones in total). The case numbers i have a conditional format set to highlight the duplicate cases red. The colors are not important, they were merely an attempt to help filter. I need to somehow find all cases where the case is a duplicate (red) but the same case does NOT contain a yellow cell.

in this example set i need to somehow filter down or somehow call out only case DL245024M because it is a duplicate value AND does not contain one of my most used worklists (yellow).

worklist	case number
APTHIN	DL245007M
APTHPVR	DL245007M
APEHPV	DL245024M
APEHPVR	DL245024M
APTHIN	DL245026M

 

[Cubist]

How do you determine the "most" vs "least" i.e. the yellow and the blue cells?

 

[RattlingCarp3048]

How do you determine the "most" vs "least" i.e. the yellow and the blue cells?

for this purpose i filtered the column and color coded them.

generally speaking how do i know which is which? i know them off the top of my head but i can easily write them down into a list if need be.

 

[Cubist]

Maybe this.

Book1
	A	B	C	D
1	APTHIN	DL24007M		DL245024M
2	APTHPVR	DL24007M		DL245024M
3	APEHPV	DL245024M
4	APEHPVR	DL245024M
5	APTHIN	DL245026M
6	APOT	DL245025M
Sheet1

Cell Formulas
Range	Formula
D1:D2	D1	=LET(f,FILTER(B1:B6,A1:A6<>"APTHIN"),u,UNIQUE(f,,1),FILTER(f,ISERROR(XLOOKUP(f,u,u))))
Dynamic array formulas.

 

[RattlingCarp3048]

This seems to be working would you mind explaining how this formula works in case i need to build it again in the future? the only part i understand is the FILTER(B1:B6,A1:A6<>"APTHIN") piece

 

[Cubist]

1) Filter out the APTHIN
2) Unique gets the distinct rows that are not duplicated.
3) It's doing a lookup between the unique list (2) and the filtered list (1)
If it's missing then it'll return an error. This means those values are duplicated since it's not in the unique list.
4) I filtered the filtered list in (1) a second time but with the condition of an error from step (3). This gives you back the duplicated values.

 

[RattlingCarp3048]

1) Filter out the APTHIN
2) Unique gets the distinct rows that are not duplicated.
3) It's doing a lookup between the unique list (2) and the filtered list (1)
If it's missing then it'll return an error. This means those values are duplicated since it's not in the unique list.
4) I filtered the filtered list in (1) a second time but with the condition of an error from step (3). This gives you back the duplicated values.

Thank you! I added in all the other worklists and it seems to be working. Thank you sooooo much

 

[Cubist]

You're welcome.

 

[RattlingCarp3048]

You're welcome.

Hello again, i was wrong in my last message. adding in the additional worklists is not working. it returns a 0. i tried both and (*) as well as or (+) but still a 0. how do i add in all the additional worklists?

=LET(f,FILTER(D2:D100000,(C2:C100000<>"APTHIN")*(C2:C100000<>"APTIS1")),u,UNIQUE(f,,1),FILTER(f,ISERROR(XLOOKUP(f,u,u))))

=LET(f,FILTER(D2:D100000,(C2:C100000<>"APTHIN")+(C2:C100000<>"APTIS1")),u,UNIQUE(f,,1),FILTER(f,ISERROR(XLOOKUP(f,u,u))))

 

[RattlingCarp3048]

You're welcome.

nevermind. im just dumb and my computer is being reallllllyy slow. user error.