Find and filter on two different unique values

[trishcollins]

I have an excel table that is almost 7000 rows. It is a list of control numbers and some have a lot of duplicates. In addition to the control ID, each of the rows is assigned to one for four categories. I have managed to create a formula that identifies the duplicates sequentially, so all new IDs start with with 1, and if there are duplicates, they are numbered 2, 3, 4, etc. For the purposes of this exercise, I want to see both only "1" but two of the four categories. Not all ID are assigned to all four categories, but you can have the same category assigned to same ID multiple times. Basically I want to filter this table to display rows 1 (AC-2 and PBMM) and row 5 (AC-2 and PBHH), as well as row 6 (AC-3 and PBMM) and row 8 (AC-3 and PBHH). And to make it more complex, if the ID doesn't isn't assigned to BOTH PBMM and PBHH, I want it filtered out. So to recap, I only want to see 2 of ever ID, one for PBMM and one for PBHH, but only if the ID number is assigned to both.

Row	ID	Category	Duplicate Sequence
1	AC-2	PBMM	1
2	AC-2	PBMM	2
3	AC-2	PBHM	3
4	AC-2	PBMH	4
5	AC-2	PBHH	5
6	AC-3	PBMM	1
7	AC-3	PBHM	2
8	AC-3	PBHH	3

 

[Cubist]

Which version of Excel are you using?

 

[trishcollins]

Which version of Excel are you using?

365

 

[Joe4]

365

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Cubist]

Try:

Book4
	A	B	C	D	E
1	ID	Category		Result
2	AC-2	PBMM		AC-2	PBMM
3	AC-2	PBMM		AC-2	PBHH
4	AC-2	PBHM		AC-3	PBMM
5	AC-2	PBMH		AC-3	PBHH
6	AC-2	PBHH
7	AC-3	PBMM
8	AC-3	PBHM
9	AC-3	PBHH
10	AC-4	PBHH
11	AC-4	PBMK
Sheet1

Cell Formulas
Range	Formula
D2:E5	D2	=UNIQUE(FILTER(A2:B11,MAP(A2:A11,B2:B11,LAMBDA(a,b,((b="PBMM")+(b="PBHH"))*SUM(--(COUNTIFS(A2:A11,a,B2:B11,{"PBMM","PBHH"})>0))=2))))
Dynamic array formulas.

 

[trishcollins]

Try:

Book4
	A	B	C	D	E
1	ID	Category		Result
2	AC-2	PBMM		AC-2	PBMM
3	AC-2	PBMM		AC-2	PBHH
4	AC-2	PBHM		AC-3	PBMM
5	AC-2	PBMH		AC-3	PBHH
6	AC-2	PBHH
7	AC-3	PBMM
8	AC-3	PBHM
9	AC-3	PBHH
10	AC-4	PBHH
11	AC-4	PBMK
Sheet1

Cell Formulas
Range	Formula
D2:E5	D2	=UNIQUE(FILTER(A2:B11,MAP(A2:A11,B2:B11,LAMBDA(a,b,((b="PBMM")+(b="PBHH"))*SUM(--(COUNTIFS(A2:A11,a,B2:B11,{"PBMM","PBHH"})>0))=2))))
Dynamic array formulas.

Thanks, will give it a try