Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to return multiple results in a cell | Excel Discussion (Misc queries) | |||
return multiple cell references | Excel Discussion (Misc queries) | |||
How to return to a cell after sorting a spreadsheet. | Excel Worksheet Functions | |||
Using a lookup to return multiple values in one cell?? | Excel Discussion (Misc queries) | |||
Using a lookup to return multiple values in one cell?? | New Users to Excel |