Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 10
Default Return multiple vales from a different spreadsheet into one cell

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
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default Return multiple vales from a different spreadsheet into one cell

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



  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 10
Default Return multiple vales from a different spreadsheet into one ce

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




  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default Return multiple vales from a different spreadsheet into one ce

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






  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 10
Default Return multiple vales from a different spreadsheet into one ce

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








  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default Return multiple vales from a different spreadsheet into one ce

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








  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 10
Default Return multiple vales from a different spreadsheet into one ce

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" wrote:

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









  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default Return multiple vales from a different spreadsheet into one ce

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


--
Biff
Microsoft Excel MVP


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

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











  #9   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default Return multiple vales from a different spreadsheet into one ce

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

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

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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)
," ","~"),","," "))," ",", "),"~"," ")


--
Biff
Microsoft Excel MVP


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

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













  #10   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 10
Default Return multiple vales from a different spreadsheet into one ce

Thanks for all you help.

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



"T. Valko" wrote:

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


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

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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)
," ","~"),","," "))," ",", "),"~"," ")


--
Biff
Microsoft Excel MVP


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

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
















  #11   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default Return multiple vales from a different spreadsheet into one ce

You're welcome!

--
Biff
Microsoft Excel MVP


"Rodders" wrote in message
...
Thanks for all you help.

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



"T. Valko" wrote:

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


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

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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)
," ","~"),","," "))," ",", "),"~"," ")


--
Biff
Microsoft Excel MVP


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

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
















Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to return multiple results in a cell Vinod[_2_] Excel Discussion (Misc queries) 5 May 28th 08 06:34 AM
return multiple cell references laszlo Excel Discussion (Misc queries) 4 September 6th 07 10:54 PM
How to return to a cell after sorting a spreadsheet. Givvie Excel Worksheet Functions 1 January 20th 07 04:06 AM
Using a lookup to return multiple values in one cell?? [email protected] Excel Discussion (Misc queries) 4 July 7th 06 01:50 PM
Using a lookup to return multiple values in one cell?? zim_zimmer New Users to Excel 1 July 7th 06 10:28 AM


All times are GMT +1. The time now is 03:15 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"