Formatting Output

[RobertWA]

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

[xenixman]

You can use the Paste Special, Transpose function.

If your data is in Columns like:

Please Login or Register  to view this content.

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

[tony h]

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

[tony h]

and a sample attached

[Rebuild8]

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.

[tony h]

Rebuild8,

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

[Rebuild8]

Cool! I knew when I was typing all that that there HAD to be a shorter way! (nothing drives me battier than a bunch of nested IF statements)

[tony h]

Glad you liked it.

[martindwilson]

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

[tony h]

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.

[RobertWA]

Thank you all for your assistance. Over the next few days I'll look at these solutions and try to decide which one suits my needs best.

Thanks again.

Robert

[RobertWA]

Deleted -- started new thread

Robert