Alternative to the V-LOOKUP? | Fairly simple table problem

[James12513]

Hello all,

I am having a bit of an issue with trying to convert one list of items into the table below.

The vlookup won't work in this case because it takes the top value it finds, so in the case of trying to find all cities for ID 123, only London will have a "Y".

Does anyone know a method whereby I can get around this issue please?

As always, will really appreciate any ideas


LIST

ID Cities visited
123 London
456 New York
789 Tokyo
123 Berlin
123 Florida

[U][B]TABLE (what I want it to show)[/B][/U]

        London     New York     Tokyo     Berlin      Florida
123       Y                                      Y            Y
456                    Y
789                               Y

 

[Andrew Poulsom]

Formula in E2 copied down and across:

Excel 2010
	A	B	C	D	E	F	G	H	I
1	ID	Cities visited			London	New York	Tokyo	Berlin	Florida
2	123	London		123	Y			Y	Y
3	456	New York		456		Y
4	789	Tokyo		789			Y
5	123	Berlin
6	123	Florida
Sheet1

Cell Formulas
Range	Formula
E2	=REPT("Y",COUNTIFS($A$2:$A$6,$D2,$B$2:$B$6,E$1)>0)

 

[James12513]

Andrew thank you so much! This is incredible
I've been trying to figure this problem out for ages now (trying to use index-match functions but to no avail).