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

joshlally

New Member
Joined
Oct 7, 2014
Messages
32
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:

townconfirmed
londonyes
manchesteryes
birminghamno

<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.
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

platonic567

Board Regular
Joined
Feb 21, 2014
Messages
153
Here's how my data is setup:


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

Code:
=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

New Member
Joined
Oct 7, 2014
Messages
32
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

New Member
Joined
Oct 7, 2014
Messages
32

ADVERTISEMENT

DEF
CITYPOSTCODEresult
HO CHI MINHyHO CHI MINH
HO CHI MINHn
HO CHI MINHn
HO CHI MINHn
HO CHI MINHn
HO CHI MINHn
HA NOIy
HA NOIy
HA LONGy
CAN THOy
HAI PHONGy
DA NANGy
BIEN HOAn
VUNG TAUn
QUI NHONn
BINH DUONGn

<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))),"")
 

joshlally

New Member
Joined
Oct 7, 2014
Messages
32

ADVERTISEMENT

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

Board Regular
Joined
Feb 21, 2014
Messages
153
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

New Member
Joined
Oct 7, 2014
Messages
32
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!



DEFGHIJ
1
CITYPOSTCODEID RESULTID
2
3
HO CHI MINHy100 HO CHI MINH100
4
HO CHI MINHn HA NOI200
5
HO CHI MINHn HA NOI200
6HO CHI MINHn HA LONG300
7HO CHI MINHn CAN THO400
8HO CHI MINHn HAI PHONG500
9HA NOIy200 DA NANG600
10HA NOIy200
11HA LONGy300
12CAN THOy400
13HAI PHONGy500
14DA NANGy600
15BIEN HOAn
16VUNG TAUn
17QUI NHONn
18BINH DUONGn

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

platonic567

Board Regular
Joined
Feb 21, 2014
Messages
153
No problem, its quite simple really.

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

Code:
=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))),"")
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,560
Messages
5,596,842
Members
414,107
Latest member
Tigretto

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top