MetaChat - Excel Help Time!

MetaChat is an informal place for MeFites to touch base and post, discuss and chatter about topics that may not belong on MetaFilter. Questions? Check the FAQ. Please note: This is important.

03 October 2006

Excel Help Time! I have a file of rows and rows of numbers. I need two blank rows between each row of text. Anyway to do this other than manually?

post by: ThePinkSuperhero at: 15:38 | 17 comments

Here's the easy manual way:

Highlight row using Shift+Space
Insert row using Ctrl+"+" (the plus sign on the number pad)

Do shift+space with your left hand, and ctrl++ with your right hand--it'll go quickly.

I'll try to think of a non-manual way.

posted by mullacc 03 October | 15:43

I think that after you do the first one, ctl-y will also just redo the insertion.

But that's still manual.

posted by occhiblu 03 October | 15:45

And of course, if there is a way, then I'm a total idiot and wasted a lot of my day :-D

posted by ThePinkSuperhero 03 October | 15:45

A macro's what you want. Let me whip you up something.

posted by flopsy 03 October | 15:46

Hmm, I don't know if this would be the quickest way, but I'd do it this way:

In a new sheet, I'm make Column a
A
1 =Sheet1, A1
2
3
4 =Sheet1, A2
5
6
7 =Sheet1, A3

(the terminology isn't right, but I'd just tab over to the first sheet and hilight the cell I wanted). Then highlight that new column and drag down to make sure it propogates right (you might have to only highlight rows 2-7) - there should now be (# of rows)*3-2, or something like that. Then, I'd highlight that whole column and drag it all the way across until I've propogated the same number of columns as the original.

A macro is def. what you want.

posted by muddgirl 03 October | 15:46

ah, that formatting totally failed on me.

posted by muddgirl 03 October | 15:47

http://download.yousendit.com/D6B87A931A1EA8BC

Press Tools, Macro, Macros. Highlight Sheet1.insert_rows and press Run. Is that what you want it to do?

posted by flopsy 03 October | 15:57

I'm useless at Excel. What I would do is export the file as a .CSV and then import it into Access. Write a query to insert a bunch of blank records and then export the new table to .CSV which could then be brought into Excel.

You can do the blank lines in the .CSV in any text editor too.

posted by Mitheral 03 October | 16:01

Ditto the export to csv, process in a real programming language, re-import. Let me know if you still need something for this later in the week Pinks and I'll write up some Python for ya!

posted by onalark 03 October | 16:05

YES that Macro is what I want. Yaaaaaaaaaay.

posted by ThePinkSuperhero 03 October | 16:06

In my ongoing war against macros, I've managed to do this using only excel functions. Here's the sample. I'd use the formulas to build the new spaced-out list, then highlight the entire tab, copy, then Edit->Paste Special->paste as values, so you end up with the values rather than the formulas linked to another tab.

posted by mullacc 03 October | 16:08

Bah, I lose to the geeks again. Good luck when your macros decide to corrupt your files.

posted by mullacc 03 October | 16:10

mwah, mullacc got my last step.

posted by muddgirl 03 October | 16:13

=IF(MOD(CELL("row",A1),3)=1,INDIRECT(CONCATENATE("'Your Sheet Here'!",ADDRESS((CELL("row",A1)+2)/3,CELL("col",A1)))),"")

posted by eamondaly 03 October | 17:10

I like your solution mullac. Sneaky.

Inspired by mullac's solution, here's another sneaky way of doing it:

- Your list is in A1 and goes down
- Put
=IF(MOD(ROW()-1,3)=0,OFFSET($A$1,(ROW()-1)/3,0),"") in B1
- Fill down (normally) as far as you need
- Copy & paste values

posted by flopsy 03 October | 17:12

Oooh snap eamondaly, almost.

posted by flopsy 03 October | 17:14

Excel cracks me up.

posted by eamondaly 03 October | 17:41

← OMG! Anime Pup! || Ping Pong Pixel →

HOME || REGISTER || LOGIN