Array of unique items by condition

[Jon von der Heyden]

Hiya.

Struggling a little with trying to geneate a list that is later to be linked to a combo box.

Using this list, I would like to create a list of unique items for Node_2 where Node_1="Crit1".

Book1
	A	B	C	D	E
1	Node_1	Node_2		Unique List
2	Crit1	Value1		Value1
3	Crit1	Value2		Value2
4	Crit2	Value3		Value6
5	Crit2	Value4			* need formula to produce unique list of Node_2 values where Node_1 = "Crit1". Notice no blank cells between values in unique list.
6	Crit2	Value5
7	Crit1	Value6
8	Crit2	Value7
9	Crit2	Value8
10	Crit2	Value9
Sheet1

Appreciate any help / referral.

Regards

 

[Excelenator]

Chip Pearson is your man for this. Check out this link.

http://www.cpearson.com/excel/duplicat.htm#ExtractingUnique

 

[Aladin Akyurek]

Book2
	A	B	C	D	E
1			0		Crit1
2	Node_1	Node_2	Idx		3
3	Crit1	Value1	1		Unique List
4	Crit1	Value2	2		Value1
5	Crit2	Value3			Value2
6	Crit2	Value4			Value6
7	Crit2	Value5
8	Crit1	Value6	3
9	Crit2	Value7
10	Crit2	Value8
11	Crit2	Value9
Sheet1

C1 must house a 0.

C3, coied down:

=IF(A3=$E$1,IF(ISNA(VLOOKUP(A3,$A$2:$B$2,2,0)),LOOKUP(9.99999999999999E+307,$C$1:C2)+1,""),"")

E2:

=LOOKUP(9.99999999999999E+307,C1:C11)

E4, copied down:

=IF(ROWS($E$4:E4)<=$E$2,LOOKUP(ROWS($E$4:E4),$C$3:$C$11,$B$3:$B$11),"")

The unique list in E can be referred to by a name...

Activate Insert|Name|Define.
Enter Crit1List in the Names in Workbook box.
Enter the following formula in the Refers to box:

=Sheet1!$E$4:INDEX(Sheet1!$E$4:$E$65536,MATCH("*",Sheet1!$E$4:$E$65536,-1))

Click OK.

Note that, if you want to extract multiple lists, say, for Crit1, Crit2, etc., a different setup is required.

 

[Jon von der Heyden]

Aladin. I've just got round to this and since going through it thoroughly I can even understand it eventually! Thanks so much

 

[Todd Bardoni]

How about:-

Book1
	A	B	C	D
1	Node_1	Node_2	Crit1	Crit2
2	Crit1	Value1	Value1	Value3
3	Crit1	Value2	Value2	Value4
4	Crit2	Value3	Value6	Value5
5	Crit2	Value4		Value7
6	Crit2	Value5		Value8
7	Crit1	Value6		Value9
8	Crit2	Value7
9	Crit2	Value8
10	Crit2	Value9
Sheet1

Formula in C2:-

=IF(COUNTIF($A$2:$A$10,C$1)<ROW()-1,"",INDEX($B$2:$B$10,SMALL(IF($A$2:$A$10=C$1,ROW($1:$9)),ROW()-1)))

Confirmed with Ctrl+Shift+Enter