Creating a Unique New List Based on a 1st List

[DaveE]

Here is what I would like to do. In the example I have created a table titled "Cities in USA by State". Using the "Data/Validation/Allow:List/Data:=States" drop down option I choose PA in cell G1. Then just below this I want to create a list that gives just those cities located in PA (by referencing cell G1) based on the master list to the left. The attached example shows what I want in the end, but in this example I just typed it in.

Make sense? Can anyone help?

Thanks,
Dave

Book3
	A	B	C	D	E	F	G	H
1	CitiesinUSA,byStateList					State	NY
2
3	Cities	NY	PA	USA		Cities	NY
4	NewYork	X		X		Pittsburgh	X
5	Pittsburgh		X	X		Philadelphia	X
6	Rochester	X		X		Scranton	X
7	Syracuse	X		X
8	Philadelphia		X	X
9	Scranton		X	X
10
Sheet1

 

[Domenic]

Try the following...

H1:

=COUNTIF(INDEX(B4:D9,0,MATCH(G1,B3:D3,0)),"x")

F4, copied down:

=IF(ROWS(F$4:F4)<=$H$1,INDEX(A$4:A$9,SMALL(IF(INDEX($B$4:$D$9,0,MATCH($G$1,$B$3:$D$3,0))="x",ROW($A$4:$A$9)-ROW($A$4)+1),ROWS(F$4:F4))),"")

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!