Return multiple vales from a different spreadsheet into one cell

[Rodders]

I am using two spreadsheets and need to return multiple cells into one cell.

I have been using vlookup to return single results and using surnames as the
common cell in both spreadsheets. I want to continue using the surname to
link to the other spreadsheet but look at four columns of data in spreadsheet
1 and return into one column in spreadsheet 2 separated by commas.

I hope this makes sense.

Thanks

 

[T. Valko]

look at four columns of data in spreadsheet 1 and return

into one column in spreadsheet 2 separated by commas.

Concatenate the lookups into one formula. Something like this:

=VLOOKUP(A1,B:F,2,0)&","&VLOOKUP(A1,B:F,3,0)&","&VLOOKUP(A1,B:F,4,0)&","&VLOOKUP(A1,B:F,5,0)

 

[Rodders]

Thanks for the reply. It is what i wanted. I have one twist that i should
have mentioned before. Not all of the columns have information in them hence
the formula below shows a small space and then comma when there is no
information(some of the data have two or three commas at the end) . How do i
make these not appear when there is no data????

 

[T. Valko]

I have one twist that i should have mentioned before.

There's always a forgotten twist! <g>

I'm hoping that the values in the cells don't already contain spaces and/or
commas!

Try something like this (split up so the needed space characters don't get
taken out by line wrap).

All on one line:

=SUBSTITUTE(TRIM(VLOOKUP(A1,B:F,2,0)&" "&
VLOOKUP(A1,B:F,3,0)&" "&
VLOOKUP(A1,B:F,4,0)&" "&
VLOOKUP(A1,B:F,5,0))," ",", ")

 

[Rodders]

Hi again,

Thanks that has worked but it bought up another problem. The commas are now
appearing between the words in each column.

For example, Column 1 Bill Bloggs
Column 2 Fred Bassett
Column 3 Jane Doe

Using the formula it is returning Bill, bloggs, Fred, Bassett, Jane, Doe
but I need
Bill Bloggs, Fred Bassett, Jane Doe

Regarding your message below there are no spaces or commas in the cells.

Thanks

 

[T. Valko]

there are no spaces or commas in the cells.

Bill Bloggs
Fred Bassett
Jane Doe

That's what I meant about spaces. The cells have names with spaces.

Well, I'm getting ready to quit for the day. I'll check back tomorrow. Maybe
someone else will chime in before then. Any more twists that we should know
about? Last call for twists! <g>

 

[Rodders]

I thought you meant in the cells with no data. So yes there are spaces
between the first and last names.

It is straight ahead from now on.

 

[T. Valko]

Ok, here you go...

All on one line.

=SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(
SUBSTITUTE(VLOOKUP(A1,B:F,2,0)&","&
VLOOKUP(A1,B:F,3,0)&","&
VLOOKUP(A1,B:F,4,0)&","&VLOOKUP(A1,B:F,5,0)
," ","~"),","," "))," ",", "),"~"," ")

 

[T. Valko]

," ","~"),","," "))," ",", "),"~"," ")

Wow! That last line is a thing of beauty, ain't it? <bg>

 

[Rodders]

Thanks for all you help.

For simple minds like mine it only adds to the confusion.

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

Thanks for all you help.

For simple minds like mine it only adds to the confusion.