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

--
Biff
Microsoft Excel MVP


"Rodders" wrote in message
...
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" wrote:

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


--
Biff
Microsoft Excel MVP


"Rodders" wrote in message
...
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" wrote:

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)&","&V LOOKUP(A1,B:F,4,0)&","&VLOOKUP(A1,B:F,5,0)

--
Biff
Microsoft Excel MVP


"Rodders" wrote in message
...
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