Copy multiple values into a single cell

[Suzanne]

I have a worksheet where:
Row 1 is the header row
A2:Axx is UIC
B2:Bxx is BLDG
C2:Cxx is ROOM
D2xx 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 where:
(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

 

[Matthew Pfluger]

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

 

[crferguson]

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

 

[ryguy7272]

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---

 

[Suzanne]

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