Find cities visited

[bren]

Hi,

I have in column A the names of people. In columna B C D the cities
that each one of them has visited.

A1 B1 C1 D1

Peter Washington Boston Chicago
John New York Blank Los
Angeles

Some cells can be blank. The question is that on a second table below
I have the names of each person.and I need a formula that shows all
the cities visited by each person one after other and without blanck
cells.

Example

If I write in A20 John, B20 and C20 must show New York and Los Angeles

Thank you very much

 

[T. Valko]

It's kind of complicated!

Let's assume your data is in the range A25

A2:A5 = Names
B25 = Cities

In the formula:

Name refers to $A$2:$A$5
City refers to $B$2:$D$5

A20 = some name like John

Enter this formula in A19. This will return the number of cities related to
John.

=SUMPRODUCT((Name=A20)*(City<>""))

Enter this array formula** in B20:

=IF(COLUMNS($B20:B20)>$A19,"",INDEX(City,MATCH($A20,Name,0),SMALL(IF(INDEX(City,MATCH($A20,Name,0),0)<>"",COLUMN(City)),COLUMNS($B20:B20))-MIN(COLUMN(City))+1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Copy across to a number of cells that is equal to the maximum number of
cities that any person may have visited. For example, if John visited the
most cities and that number is 10, then you have to copy the formula to at
least 10 cells.

Here's a small sample file that demonstrates this.

xBren.xls 14 kb

http://cjoint.com/?mptWD02iYT

 

[Bernard Liengme]

While I am in awe of BIff's result here is my offering,
I need 2 row to do the job simply
In A21 I enter the person's name (e.g.John)
In A20 I have =MATCH($A21,$A$1:$A$18,0) while tells me the row that the
person is found
In B2 I have =COUNTA(INDEX(B$1:B$18,$A$20)) which basiclly tells me if the
first city is blank or not
In C2 =B20+COUNTA(INDEX(C$1:C$18,$A$20))
In D2 =C20+COUNTA(INDEX(D$1$18,$A$20))

In B21
=IF(B20=1,INDEX(B$1:B$18,$A$20),IF(C20=1,INDEX(C$1:C$18,$A$20),IF(D20=1,INDEX(D$1$18,$A$20),"")))
In C21
=IF(C20*B20=2,INDEX(C$1:C$18,$A$20),IF(D20=2,INDEX(D$1$18,$A$20),""))
in D21
=IF(D20=3,INDEX(D$1$18,$A$20),"")

This worked on the data below ( where < > denotes blank) , which I think
covers all possibilities
Peter Washington Boston Chicago
John New York < > Los Angeles
Ann < > Toronto Montreal
Jack Tokyo < > < >
Emma < > < > London
Dan Waterloo Halifaxbest wishes

Feel free to email me for sample file (email addy from website)--
Bernard Liengme
http://people.stfx.ca/bliengme
Microsoft Excel MVP

 

[bren]

Thank you so much. That was awesome. I tried the array formula and it
works pretty good. Is there any way I could get the results sorted or
should I do that manually?

Thank you so much for your support. You´ve saved me a lot of time.

 

[Bernard Liengme]

If you want sorting then we would need a VBA function
If there is time this week I will try. Email be directly
Bernard