Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy multiple values into a single cell
I have a worksheet whe
Row 1 is the header row A2:Axx is UIC B2:Bxx is BLDG C2:Cxx is ROOM D2:Dxx is LAST [name] E2:Exx is FIRST [name] F2:Fxx is MI [middle initial] I've tried to get a merge to work in Word, but have been told what I need to do is impossible (Word cannot merge data from two separate fields). So, perhaps this might work from the Excel side. I'm holding out for some hope that Excel can group multiple values on another worksheet -- otherwise, I'm stuck having to copy paste data onto each form in Word. Can I get the following results from Excel?: Column A: UIC & BLDG & ROOM Column B: LAST & FIRST & MI The UIC & ROOM will always be in the same BLDG The UIC is not the same for each ROOM (this is what Word merge can't do) The output would look something like: (Header Row) A1: UIC; BLDG; ROOM B1: LAST, FIRST, MI A2: W1234; 456; 12A B2: Jones, Mary L Smith, Joe S Doe, Adam D A3: W3456; 456; 12A B3: Black, Alan F White, Barbara T A4: W3456; 456; 99A B4: House, Jill S [and so on] You see that: o The BLDG stayed the same for all three rows; o Rows 2 & 3 have the same ROOM; o Rows 3 & 4 have the same UIC but not the same room; o The names column B are stacked (not one long string) o There is a separate form for each UIC + ROOM BTW: I also have the form in Excel whe (First page) K3 = UIC P3 = BLDG U3 = ROOM (note: I can merge these together if needed) (Second page) A52:Axx = LAST G52:Gxx = FIRST N52:Nxx = MI (note: I can merge these together if needed) Thanks -- Suzanne |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy multiple values into a single cell
Have you tried using the "&" operator? For example, to get LAST & FIRST &
MI, use: "=Dxx & ", " & Exx & " " & Fxx" where 'x' is the row number and D,E,& F refer to the correct sheet. As to putting multiple values in the same cell, yes, you can do that. To enter multiple lines in the same cell, use ALT+ENTER. I would caution you to think about why you want multiple people in the same cell. You will likely need to use a macro to enter the multiple names quickly enough, but if you use the "&" operator, you only need formulas. Instead of multiple entries per cell, consider sorting and using functions. BTW, if you do decide to use macros, the VBA equivalent of ALT+ENTER is CHR(10). HTH, Matthew Pfluger "Suzanne" wrote: I have a worksheet whe Row 1 is the header row A2:Axx is UIC B2:Bxx is BLDG C2:Cxx is ROOM D2:Dxx is LAST [name] E2:Exx is FIRST [name] F2:Fxx is MI [middle initial] I've tried to get a merge to work in Word, but have been told what I need to do is impossible (Word cannot merge data from two separate fields). So, perhaps this might work from the Excel side. I'm holding out for some hope that Excel can group multiple values on another worksheet -- otherwise, I'm stuck having to copy paste data onto each form in Word. Can I get the following results from Excel?: Column A: UIC & BLDG & ROOM Column B: LAST & FIRST & MI The UIC & ROOM will always be in the same BLDG The UIC is not the same for each ROOM (this is what Word merge can't do) The output would look something like: (Header Row) A1: UIC; BLDG; ROOM B1: LAST, FIRST, MI A2: W1234; 456; 12A B2: Jones, Mary L Smith, Joe S Doe, Adam D A3: W3456; 456; 12A B3: Black, Alan F White, Barbara T A4: W3456; 456; 99A B4: House, Jill S [and so on] You see that: o The BLDG stayed the same for all three rows; o Rows 2 & 3 have the same ROOM; o Rows 3 & 4 have the same UIC but not the same room; o The names column B are stacked (not one long string) o There is a separate form for each UIC + ROOM BTW: I also have the form in Excel whe (First page) K3 = UIC P3 = BLDG U3 = ROOM (note: I can merge these together if needed) (Second page) A52:Axx = LAST G52:Gxx = FIRST N52:Nxx = MI (note: I can merge these together if needed) Thanks -- Suzanne |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy multiple values into a single cell
In spite of your detailed explanation, I'm not quite sure what you're
asking for. But, I'll take a shot at it. It sounds like you're just trying to get the info in your Excel workbook into a format that a Word merge document of yours can handle. With the sheet where the info is in columns A-F, how about trying something like this which you can adapt to work for the other sheets: 1. Insert two new columns as A and B (highlight columns A and B and then Ctrl/Shift/+) 2. Fill in your headers in A1 and B1 3. Enter this in A2: =C2 & D2 & E2 4. Enter this in B2: =F2 & G2 & H2 5. Copy those formulas to the bottom of your data. You should then be able to merge the data into the Word document setting your source as columns A and B. I might be way off in what you're trying to accomplish, but I hope that helps. Cory On Sep 27, 7:11 am, Suzanne wrote: I have a worksheet whe Row 1 is the header row A2:Axx is UIC B2:Bxx is BLDG C2:Cxx is ROOM D2:Dxx is LAST [name] E2:Exx is FIRST [name] F2:Fxx is MI [middle initial] I've tried to get a merge to work in Word, but have been told what I need to do is impossible (Word cannot merge data from two separate fields). So, perhaps this might work from the Excel side. I'm holding out for some hope that Excel can group multiple values on another worksheet -- otherwise, I'm stuck having to copy paste data onto each form in Word. Can I get the following results from Excel?: Column A: UIC & BLDG & ROOM Column B: LAST & FIRST & MI The UIC & ROOM will always be in the same BLDG The UIC is not the same for each ROOM (this is what Word merge can't do) The output would look something like: (Header Row) A1: UIC; BLDG; ROOM B1: LAST, FIRST, MI A2: W1234; 456; 12A B2: Jones, Mary L Smith, Joe S Doe, Adam D A3: W3456; 456; 12A B3: Black, Alan F White, Barbara T A4: W3456; 456; 99A B4: House, Jill S [and so on] You see that: o The BLDG stayed the same for all three rows; o Rows 2 & 3 have the same ROOM; o Rows 3 & 4 have the same UIC but not the same room; o The names column B are stacked (not one long string) o There is a separate form for each UIC + ROOM BTW: I also have the form in Excel whe (First page) K3 = UIC P3 = BLDG U3 = ROOM (note: I can merge these together if needed) (Second page) A52:Axx = LAST G52:Gxx = FIRST N52:Nxx = MI (note: I can merge these together if needed) Thanks -- Suzanne |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy multiple values into a single cell
Try the & operator:
=A2&B2&C2 or =A2&" "&B2&" "&C2 Alternatively, you can try the Concatenate function: =CONCATENATE("This is my data ",A2," ",B2," ",C2) Hope that helps. Ryan--- -- RyGuy "Suzanne" wrote: I have a worksheet whe Row 1 is the header row A2:Axx is UIC B2:Bxx is BLDG C2:Cxx is ROOM D2:Dxx is LAST [name] E2:Exx is FIRST [name] F2:Fxx is MI [middle initial] I've tried to get a merge to work in Word, but have been told what I need to do is impossible (Word cannot merge data from two separate fields). So, perhaps this might work from the Excel side. I'm holding out for some hope that Excel can group multiple values on another worksheet -- otherwise, I'm stuck having to copy paste data onto each form in Word. Can I get the following results from Excel?: Column A: UIC & BLDG & ROOM Column B: LAST & FIRST & MI The UIC & ROOM will always be in the same BLDG The UIC is not the same for each ROOM (this is what Word merge can't do) The output would look something like: (Header Row) A1: UIC; BLDG; ROOM B1: LAST, FIRST, MI A2: W1234; 456; 12A B2: Jones, Mary L Smith, Joe S Doe, Adam D A3: W3456; 456; 12A B3: Black, Alan F White, Barbara T A4: W3456; 456; 99A B4: House, Jill S [and so on] You see that: o The BLDG stayed the same for all three rows; o Rows 2 & 3 have the same ROOM; o Rows 3 & 4 have the same UIC but not the same room; o The names column B are stacked (not one long string) o There is a separate form for each UIC + ROOM BTW: I also have the form in Excel whe (First page) K3 = UIC P3 = BLDG U3 = ROOM (note: I can merge these together if needed) (Second page) A52:Axx = LAST G52:Gxx = FIRST N52:Nxx = MI (note: I can merge these together if needed) Thanks -- Suzanne |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy multiple values into a single cell
Thanks to all!
YES! I am trying to force something into Word that it doesn't want to do. I already put another column on the worksheet: "UICROOM" (UIC & ROOM) I can now run a merge report which contains the names of individuals in the same UIC & ROOM -- but I've only been able to do it on plain paper. I need to get this list of names on the second page of a 2-page form (the first page contains UIC, BLDG, ROOM and many, many rows/columns of other static data) I've spent MUCH time trying to get Word to automate this. If there is a way to do it, a large cell with a stacked list of names associated with "UICROOM" will allow me to do an easier, much simplified merge. BTW: I have no problem getting the UIC, BLDG, ROOM on the first page and I can get a single name assigned to each UICROOM on the second page. I need ALL names associated with the UICROOM. Suzanne "Suzanne" wrote: I have a worksheet whe Row 1 is the header row A2:Axx is UIC B2:Bxx is BLDG C2:Cxx is ROOM D2:Dxx is LAST [name] E2:Exx is FIRST [name] F2:Fxx is MI [middle initial] I've tried to get a merge to work in Word, but have been told what I need to do is impossible (Word cannot merge data from two separate fields). So, perhaps this might work from the Excel side. I'm holding out for some hope that Excel can group multiple values on another worksheet -- otherwise, I'm stuck having to copy paste data onto each form in Word. Can I get the following results from Excel?: Column A: UIC & BLDG & ROOM Column B: LAST & FIRST & MI The UIC & ROOM will always be in the same BLDG The UIC is not the same for each ROOM (this is what Word merge can't do) The output would look something like: (Header Row) A1: UIC; BLDG; ROOM B1: LAST, FIRST, MI A2: W1234; 456; 12A B2: Jones, Mary L Smith, Joe S Doe, Adam D A3: W3456; 456; 12A B3: Black, Alan F White, Barbara T A4: W3456; 456; 99A B4: House, Jill S [and so on] You see that: o The BLDG stayed the same for all three rows; o Rows 2 & 3 have the same ROOM; o Rows 3 & 4 have the same UIC but not the same room; o The names column B are stacked (not one long string) o There is a separate form for each UIC + ROOM BTW: I also have the form in Excel whe (First page) K3 = UIC P3 = BLDG U3 = ROOM (note: I can merge these together if needed) (Second page) A52:Axx = LAST G52:Gxx = FIRST N52:Nxx = MI (note: I can merge these together if needed) Thanks -- Suzanne |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLookup multiple values - sum returned values into single cell | Excel Worksheet Functions | |||
Multiple returned values into a single cell | Excel Discussion (Misc queries) | |||
Auto copy single values to another cell | Excel Discussion (Misc queries) | |||
How to sum the values of a single cell from multiple worksheets | Excel Worksheet Functions | |||
Toggle multiple values in single cell | Excel Worksheet Functions |