Solving excel troubles by thinking out of the box

A friend of mine just pinged me, wanting to know if I knew a way to programmatically add a range of number sequences to an excel worksheet.

I know nothing about excel scripting (or VBA-stuff or that type of things). But that didn’t stop me from getting an idea.

So I questioned him relentlessly until I was sure I knew what the expected output should be.

What he wanted was 16560 rows  in a worksheet. Rows 1 through 60 should contain the number 1. Rows 61 to 120 should contain the number 2, etc…

The final 60 rows should contain the number 276.

Why would he want this? I don’t know, some school work I presume. I didn’t particularly care, I assume his teacher wanted that format and was to lazy to provide a template…

I did however care that I found the problem interesting. How could I help, without any knowledge in VBA-scripting, and no desire to learn it within the next two hours?

The solution was rather neat:

$ touch file.csv
$ for i in `seq 1 276`;
do 
    for a in `seq 1 60`;
    do
        echo "$i" >> file.csv
    done
done
$ unix2dos file.csv

After having created the file, ensured it would work on his windows system, and transferred it to him (and he verified that it worked to import it and copy the results over to where he needed to work with it) I timed the execution of the loop.

2.33 seconds, against several hours if doing it manually, or $DEITY knows how long if it involved learning how to create a VBA script to do it for you…

He was pleased, I had fun, all in all 25 well spent minutes. Win-win.

And it was definitely easier, for me, to create a csv-file for him to import, than to learn me sum VBA.

:wq

Tags: , ,

4 Responses to “Solving excel troubles by thinking out of the box”

  1. Nice story, great hack! :D

    Also I love the $DEITY variable. …have you noticed how “:wq” has almost a “QED” notion? ;)

  2. Patrik says:

    Thank you, I thought it a rather neat solution :)
    The $DEITY variable is as important as the $EDITOR variable, great for letting everyone make up their own mind about what the right thing is ;D
    I never thought about :wq that way, but now that you mention it… ;D
    “what was to be written and closed” ;D

  3. I used to use “god(s)”, but “$DEITY” is quite nifty :]

    Hehe, yeah “:wq” has kinda the “I have spoken!” Notion to it :D

  4. Patrik says:

    Yeah, $DEITY is neat, I just wish I could remember who I got it from.
    “I have spoken!” I like that :D