Urgent help please! I need a very specific 'if' formula

[joshlally]

Hi all,

Thanks for taking the time to try and help me out. I basically have an excel sheet with a list of towns on it, and a 'yes/no' column for each of the rows containing towns. What I would like to do is (on a separate sheet, or a separately linked workbook if possible) create a formula which checks to see if an of the towns i have listed have a yes or no by them; and if yes - copy the town name into a column of its own. I have attached a mock up table showing what i described above if anyone could help!

Many thanks for all your time.

Josh

Example:

town	confirmed
london	yes
manchester	yes
birmingham	no

<tbody>
</tbody>

...and because rows 3 and 4 are 'yes' I would like the exact text in those cells to show up on a separate sheet.

 

[joshlally]

Bump!

 

[platonic567]

Here's how my data is setup:


Array-Entered Formula is D2, be sure to confirm with CTRL+SHIFT+ENTER:

=IFERROR(INDEX($A$2:$A$5,SMALL(IF($B$2:$B$5="Yes",ROW($A$2:$A$5)-ROW($A$2)+1),ROWS($A$1:A1))),"")

	A	B	C	D
1	town	confirmed		Town
2				london
3	london	yes		manchester
4	manchester	yes
5	birmingham	no

<TBODY>
</TBODY>

 

[joshlally]

Thanks so much! Only problem is when I apply it to my table I only get the result from A2, and not the result from the array.

 

[joshlally]

D	E	F
CITY	POSTCODE	result
HO CHI MINH	y	HO CHI MINH
HO CHI MINH	n
HO CHI MINH	n
HO CHI MINH	n
HO CHI MINH	n
HO CHI MINH	n
HA NOI	y
HA NOI	y
HA LONG	y
CAN THO	y
HAI PHONG	y
DA NANG	y
BIEN HOA	n
VUNG TAU	n
QUI NHON	n
BINH DUONG	n

<tbody>
</tbody>

This is the code I altered . The letter headings indicate the column letters.

=IFERROR(INDEX($D$2:$D$17,SMALL(IF($E$2:$E$17="y",ROW($D$2:$D$17)-ROW($D$2)+1),ROWS($D$1:D1))),"")

 

[mikerickson]

Advanced filter will do that.

 

[joshlally]

Hey Mike, The problem is I need it to be on a separate sheet or even a linked workbook (even better) so that someone can access it without actually touching the source data.

 

[platonic567]

D	E	F
CITY	POSTCODE	result
HO CHI MINH	y	HO CHI MINH
HO CHI MINH	n
HO CHI MINH	n
HO CHI MINH	n
HO CHI MINH	n
HO CHI MINH	n
HA NOI	y
HA NOI	y
HA LONG	y
CAN THO	y
HAI PHONG	y
DA NANG	y
BIEN HOA	n
VUNG TAU	n
QUI NHON	n
BINH DUONG	n

<TBODY>
</TBODY>

This is the code I altered . The letter headings indicate the column letters.

=IFERROR(INDEX($D$2:$D$17,SMALL(IF($E$2:$E$17="y",ROW($D$2:$D$17)-ROW($D$2)+1),ROWS($D$1:D1))),"")

Everything is ok, you just need to confirm the formula with CTRL+SHIFT+ENTER and not just ENTER.

 

[joshlally]

Platonic, you're and absolute genius! Thank you so much. At the risk you sounding cheeky I was wondering if you could help me with one more issue? So now these cities have been generated from your formula(I)- If I was to add a separate column to input data next to the result (J) - Is there a way that I can get that data to find the row that the city was generated from (say HA NOI), and place the data on the row but in a separate column (F). In short can I get data entered into the J column to match up with the place it was generated from in the ID column as demonstrated in the table? Thanks again Platonic!

	D	E	F	G	H	I	J
1	CITY	POSTCODE	ID			RESULT	ID
2
3	HO CHI MINH	y	100			HO CHI MINH	100
4	HO CHI MINH	n				HA NOI	200
5	HO CHI MINH	n				HA NOI	200
6	HO CHI MINH	n				HA LONG	300
7	HO CHI MINH	n				CAN THO	400
8	HO CHI MINH	n				HAI PHONG	500
9	HA NOI	y	200			DA NANG	600
10	HA NOI	y	200
11	HA LONG	y	300
12	CAN THO	y	400
13	HAI PHONG	y	500
14	DA NANG	y	600
15	BIEN HOA	n
16	VUNG TAU	n
17	QUI NHON	n
18	BINH DUONG	n

<colgroup><col span="2"><col><col><col><col span="2"></colgroup><tbody>
</tbody>

 

[platonic567]

No problem, its quite simple really.

Formula in J2, as before confirm with CTRL+SHIFT+ENTER:

=IFERROR(INDEX($F$3:$F$18,SMALL(IF($E$3:$E$18="y",ROW($D$3:$D$18)-ROW($D$3)+1),ROWS($D$1:D1))),"")