I have me an xls table, I do!

May 17, 2009 4:59 PM

I have an xls file I got from a survey I created on Google docs. Now I want to make it pretty without copy-pasting every bloody cell into an Indesign document…

So, using Apples Numbers, I'm trying to change a ≈30×20 table which looks like this:


__________| Q1 | Q2 | Q3 | Q4 |
Timestamp | A1 | A2 | A3 | A4 |
Timestamp | B1 | B2 | B3 | B4 |
Timestamp | C1 | C2 | C3 | C4 |

Into something that looks like this, with each question on a separate page:

Q1:
A1
B1
C1
D1

Q2:
A2
B2
C2
D2

etc.


As well as a version with each timestamp/user on a seperate page:

Q1:
A1

Q2:
A2

Q3:
A3

etc.

Having looked through the Apple tutorials on Numbers as well as browsing the Google docs forums, I'm stumped. Oh ye wizards of tabular data, aid me in this time of need!

I could use Neo Office or any other data-shuffling method (maybe this can be manhandled by egrep in Textwrangler?) as long as I get a result that is printable / pdf-able.

All the answers are of different lenght, (as in B1 might be "yes" while B2 is three paragraphs long) which need to be accounted for. If there's a way to set type, style and so forth at the same time, that would be dandy!

posted by monocultured to computers & internet (7 comments total)

Here's a very similar question I asked on this topic.
posted by lunchbox at 5:09 PM on May 17

Yeah, that sounds like what I'm after. Don't have Word though, and when I try to find something corresponding in NeoOffice I get stuck at Insert > Field > Other… And can't do much more than importing the name of the table.

Part of the problem is that I don't know which words to use to describe what I want. "Make it pretty" isnt' much of a description, and "format" seems to mean something specific in Excel-land.
posted by monocultured at 5:33 PM on May 17

Not sure if this is workable as I'm not in front of my Mac at the moment... but it looks like Pages can do a Mail Merge from a Numbers document, which you might be able to massage into doing what you want.
posted by agentmunroe at 7:02 PM on May 17

I just did part 1 of this yesterday: Q1 A1 B1 C1 on the same page:

1) Do "save a copy" from your original Google results spreadsheet.

2) Insert a new blank row after each response.

3) Fill the first inserted row with something like "-----" across all question columns.

4) Copy and paste this row into all of the other blank rows you created in 2).

5) Copy the column of a question you want to show in the Q1 A1 A2 A3 format.

6) Paste this into a Google word processing doc. It will show up without the line breaks. Something like:

How old are you? 14 ----- 15 ----- 64 ----- 45 ----- 18

7) Do this for all of the columns you want to appear in the doc.

8) Now in the word processing doc, select Edit -> Edit HTML. Copy and paste the HTML for your doc into your favorite text editor.

9) Find "------" and replace it with
(or

if you want a blank line between each response.)

10) Copy and paste this back into the "Edit HTML" window for your Google Doc and hit "Update"

11) Scroll through your doc and make the questions bold and/or other clean up.

12) If you have responses that are shown as graphs in survey results sheet Form -> Show Summary, you can simply copy and paste those right from the summary into your word processing doc.

HTH!
posted by nonmyopicdave at 7:23 PM on May 17

Ack! My html didn't show up.

9) Find "------" and replace it with "<br>" (or "<br> <br>" if you want a blank line between responses)
posted by nonmyopicdave at 7:39 PM on May 17

For your second requirement: Q1 A1, Q2 A2, etc. note that you can add a new sheet to your survey results and reference a cell in the results there using "=sheet1!A2".

So you can rejigger things, paste rows/columns into a word processing doc, and use the Edit HTML method above to clean things up.
posted by nonmyopicdave at 7:47 PM on May 17

Thanks for the suggestions nonmyopicdave & lunchbox, much appreciated.

In the end I did it by hand using Pages and a lot of cursing. My inner geek was a bit selfconscious about it, but the brute force guy took over and got it done even though he felt stupid about it.

Oh, and Excel can go stab itself through the head. Copying the row as an image? Really?
posted by monocultured at 7:30 AM on May 18