Filter Unique Values Based on Different Values

[abrig005]

Hello,

Trying to figure out a formula to do the following:
Custom filter Event ID and when there is more than 1 Case Status only show the Substantiated ones as in the pic below.
Assume Event ID is Column A and Case Status is Column B


Thank you!

 

[Peter_SSs]

See if this does what you want. Both formulas copied down.

Excel Workbook
	A	B	C	D	E
1	Event ID	Status		Event ID	Status
2	0002000000000	Unsubstantiated		0002000000000	Substantiated
3	0002000000000	Unsubstantiated		0002000000001	Substantiated
4	0002000000000	Unsubstantiated		0002000000002	Substantiated
5	0002000000000	Substantiated		0002000000003	Substantiated
6	0002000000001	Substantiated		0002000000004	Unsubstantiated
7	0002000000001	Substantiated		0002000000005	Unsubstantiated
8	0002000000002	Unsubstantiated		0002000000006	Substantiated
9	0002000000002	Substantiated		0002000000007	Unsubstantiated
10	0002000000002	Substantiated		0002000000008	Substantiated
11	0002000000002	Unsubstantiated		0002000000009	Unsubstantiated
12	0002000000003	Substantiated		0002000000010	Unsubstantiated
13	0002000000003	Substantiated
14	0002000000003	Unsubstantiated
15	0002000000004	Unsubstantiated
16	0002000000004	Unsubstantiated
17	0002000000005	Unsubstantiated
18	0002000000006	Substantiated
19	0002000000007	Unsubstantiated
20	0002000000007	Unsubstantiated
21	0002000000007	Unsubstantiated
22	0002000000007	Unsubstantiated
23	0002000000007	Unsubstantiated
24	0002000000008	Unsubstantiated
25	0002000000008	Substantiated
26	0002000000009	Unsubstantiated
27	0002000000009	Unsubstantiated
28	0002000000010	Unsubstantiated
Filter List

BTW, you will generally get more helpers and faster help if you provide sample data in a form that can be copied to test with. Most helpers are not excited about typing out sample data.