Alternative to the V-LOOKUP? | Fairly simple table problem (PART 2)

[James12513]

Hello all,

I am trying to match each city to whether it rained or not. I can't seem to find a function that will allow me to do that - I've tried the V-Lookup and Index-Match functions but have had no luck...

Andrew posted a very nice formula which populates the 'cities visited' cells in table 2 below: =REPT("Y",COUNTIFS($G$2:$G$6,$K2,$H$2:$H$6,L$1)>0)

Does anyone know a formula that would populate each rain column in table 2?

Any ideas would be fantastic

Data table1:

Country ID	Cities visited	Rain?
H240	London	Y
H240	New York	N
G456	Tokyo	Y
H240	Sydney	N
W869	Florida	N

<tbody>
</tbody>

Presentation table 2:

Country ID	London	Rain?	New York	Rain?	Tokyo	Rain?	Sydney	Rain?	Florida	Rain?
W869									Y	N
G456					Y	Y
H240	Y	Y	Y	N			Y	N

<tbody>
</tbody>

 

[VBA Geek]

<b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B10</th><td style="text-align:left">{=MID(<font color="Blue">IFERROR(<font color="Red">INDEX(<font color="Green">"Y"&$C$2:$C$6,MATCH(<font color="Purple">$A10&OFFSET(<font color="Teal">$B$9,0,INT(<font color="#FF00FF">(<font color="Navy">COLUMNS(<font color="Blue">$B9:B9</font>)-1</font>)/2</font>)*2</font>),$A$2:$A$6&$B$2:$B$6,0</font>)</font>),""</font>),MOD(<font color="Red">COLUMNS(<font color="Green">$B9:B9</font>)-1,2</font>)+1,1</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.</td></tr></table><br />

Excel 2013
	A	B	C	D	E	F	G	H	I	J	K
1	Country ID	Cities visited	Rain?
2	H240	London	Y
3	H240	New York	N
4	G456	Tokyo	Y
5	H240	Sydney	N
6	W869	Florida	N
7
8
9	Country ID	London	Rain?	New York	Rain?	Tokyo	Rain?	Sydney	Rain?	Florida	Rain?
10	W869									Y	N
11	G456					Y	Y
12	H240	Y	Y	Y	N			Y	N
Sheet1

 

[James12513]

VBA Geek that is amazing, works a treat! thank you!

I don't know if it's too much to ask but would it be possible to give an explanation as to how exactly the formula is doing this beautiful magic?

 

[James12513]

Just to double check, is it correct that I have entered it into cell B10 and just dragged across and down?

Otherwise I have just done this for each column:
(Both ways seem to work? )

B10: =MID(IFERROR(INDEX("Y"&$C$2:$C$6,MATCH($A10&OFFSET($B$9,0,INT((COLUMNS($B9:B9)-1)/2)*2),$A$2:$A$6&$B$2:$B$6,0)),""),MOD(COLUMNS($B9:B9)-1,2)+1,1)

C10: =MID(IFERROR(INDEX("Y"&$C$2:$C$6,MATCH($A10&OFFSET($B$9,0,INT((COLUMNS($B9:C9)-1)/2)*2),$A$2:$A$6&$B$2:$B$6,0)),""),MOD(COLUMNS($B9:C9)-1,2)+1,1)

	A	B	C
9	Country ID	London	Rain?
10	W869
11	G456
12	H240	Y	Y

<tbody>
</tbody>

 

[VBA Geek]

Just to double check, is it correct that I have entered it into cell B10 and just dragged across and down?

Yes.

would it be possible to give an explanation as to how exactly the formula is doing this beautiful magic?

maybe you can tell me the parts that you do not understand and I answer you?