List of Unique Numbers Based on Multiple Criteria

[TonyB1965]

I need to look at data and return a list of unique numbers from column C where Column A, B match certain criteria. And a different set of returns where A,B, and D match certain criteria.

I have data that basically looks like this:

	A	B	C	D
Criteria1	Criteria 2	Criteria 3	Criteria 4
h	r
h	p	c
h	r
h	p
h	r
h	p	c
h	r
h	p
h	r
h	p
h	r
h	p
v	r
v	p	c
v	r
v	p	c
v	r
v	p
v	r
v	p
v	r
v	p	c
v	r
v	p	c
v	r

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]22[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]8[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]25[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]8[/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]22[/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]26[/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]7[/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]12[/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]

[TD="align: right"]88[/TD]

[TD="align: center"]16[/TD]

[TD="align: right"]13[/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]

[TD="align: right"]88[/TD]

[TD="align: center"]18[/TD]

[TD="align: right"]12[/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]

[TD="align: right"]88[/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]

[TD="align: right"]13[/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]

[TD="align: right"]15[/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]

[TD="align: right"]18[/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]

[TD="align: right"]3[/TD]

[TD="align: center"]24[/TD]

[TD="align: right"]20[/TD]
[TD="align: right"][/TD]

[TD="align: center"]25[/TD]

[TD="align: right"]4[/TD]

[TD="align: center"]26[/TD]

[TD="align: right"]26[/TD]
[TD="align: right"][/TD]

</tbody>

And I need results like this:

	F

<tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Result 1 (Cirteria 1=h and Criteria 2=r)[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]8[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]7[/TD]

</tbody>

[TABLE="width: 85%"]
<tbody>[TR]
[TD]

	F

<tbody>
[TD="align: center"]22[/TD]
[TD="align: center"]Result 5 (Cirteria 1=h and Criteria 2=p and Criteria 4 =c)[/TD]

[TD="align: center"]23[/TD]
[TD="align: center"]22[/TD]

[TD="align: center"]24[/TD]
[TD="align: center"]25[/TD]

</tbody>

[/TD]
[/TR]
</tbody>[/TABLE]


Would greatly prefer to use formulas and not VBA.

Thanks in advance if you can help.

 

[Eric W]

See if this works for you:

Book1
	A	B	C	D	E	F	G	H	I
1	Criteria1	Criteria 2	Criteria 3	Criteria 4		Criteria 1	Results	Criteria 1	Results
2	h	r	2			h	2	h	22
3	h	p	22	c		Criteria 2	8	Criteria 2	25
4	h	r	2			r	7	p
5	h	p	2					Criteria 4
6	h	r	8					c
7	h	p	25	c
8	h	r	8
9	h	p	22
10	h	r	2
11	h	p	26
12	h	r	7
13	h	p	0
14	v	r	12
15	v	p	88	c
16	v	r	13
17	v	p	88	c
18	v	r	12
19	v	p	88
20	v	r	13
21	v	p	15
22	v	r	18
23	v	p	3	c
24	v	r	20
25	v	p	4	c
26	v	r	26
Sheet3

Cell Formulas
Range	Formula
I2	=IFERROR(INDEX($C:$C,AGGREGATE(15,6,ROW($C$2:$C$26)/(($A$2:$A$26=H$2)*($B$2:$B$26=H$4)*($D$2:$D$26=H$6)*(COUNTIF(I$1:I1,$C$2:$C$26)=0)),1)),"")
G2	=IFERROR(INDEX($C:$C,AGGREGATE(15,6,ROW($C$2:$C$26)/(($A$2:$A$26=F$2)*($B$2:$B$26=F$4)*(COUNTIF(G$1:G1,$C$2:$C$26)=0)),1)),"")

 

[TonyB1965]

Works perfectly. Thank you very much.

 

[Eric W]

You're welcome.