Posts Tagged ‘excel’

Solving excel troubles by thinking out of the box

Saturday, July 2nd, 2011

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`;
    for a in `seq 1 60`;
        echo "$i" >> file.csv
$ 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.