Thread: Formatting Output

Hi all

I am using Excel 2007.

I have a simple spreadsheet with fields (columns) for First Name, Last Name, Address, State, phone, etc. I want to produce a list from this data in the following format:

John Smith
123 Hay Street
Melbourne
Victoria
Phone 03 9999 9999

with about 10 of these entries per page.

I know that I could do this in Access with a report, but can I do this in Excel? If so, how?

Alternatively, can I do it in Word? I know I could use Mail Merge if I wanted only one entry per page, (as if I was printing envelopes) but is there a way to do this and get 10 entries in a list on a page?

Thanks in advance for any assistance.

Robert

You can use the Paste Special, Transpose function.

If your data is in Columns like:

A	              B	             C	                D	        E	         F
First Name	 Last Name	 Address	        City	        State	        Phone
John	         Smith	         23 Hay Street          Melbourne	Victoria	3 9999 9999

Then:
On the Task Bar
Edit
Copy - your name row from A to F

Next, move to an open Cell/Column like H1
Then:
On Task Bar
Edit
Past Special
Select Transpose, it's at the bottom.

Your name in column H should now be in the form:
H
John
Smith
23 Hay Street
Melbourne
Victoria
3 9999 9999

Assuming you are expecting to keep the table as it is then:
1. on a new sheet (for preference)
2. Select column A and set the format to WRAP TEXT and make the column wide enough for your longest line
3. format each address into a single cell using =CONCATENATE(Data!A1,CHAR(10),Data!B1,CHAR(10),Data!C1,CHAR(10),Data!D1)

where the sheet containg the table is "Data" and A1 contains the first value.
Extend the Concatenate statement for as many cells as needed.
The CHAR(10) makes the text on a new line (not a new cell)

Copy the formula down.

On the Page Setup tab make sure Gridlines...Page is ticked.

You can now see where the page breaks are.

adjust the : text size, cell size etc to get 12 to a page

hope this helps

and a sample attached

If you wish to place each item in a different cell, open this workbook, and copy and paste the formula that is in I2. (Do not paste in the top row) Then copy down.

Rebuild8,

I was looking at your solution and thought I would add a couple of alternatives for interest.

Glad you liked it.

without offset use in row 1
=IF(MOD(ROW(),7)=0,"",INDEX(A:F,CEILING(ROW()/7,1),MOD(ROW(),7)))

Hi,

I have collected all the options (so far) into one sample book (attached). I'd be interested to know which one you go for.

Deleted -- started new thread

Robert