|Posted by:||Tobias Oed (tobi…@physics.odu.edu)|
|Date:||Thu, 27 May 2004|
Hello! The other day my girlfriend had to do some excel stuff. Curiosity
got the best of me and I had her explain what she was doing.
Here is a simplified version of her ordeal:
She had one hand written table with ~1000 rows and 2 columns: one for a code
and one for a description.
She also had a text file with ~16000 rows and 2 columns with the same codes
and similar (but different wording) descriptions.
She needed to produce one spread sheet with the ~1000 rows of the first spread
sheet and 3 columns: the code and the 2 descriptions.
After fat fingering a row of the hand written table she would go over to a
text editor, search for the line with the code and copy paste the alternate
description into excel.
I thought that there had to be a way to avoid that last step and get it to
be done automatically. Unfortunately I'm a unix guy and don't know jack
about excel. After messing arround with it for a bit, I managed to import
the text file in a second spreadsheet (lookup).
Then I came up with the following formulae for D1 of her fat fingered
VLOOKUP(A1, lookup!$A:$B, 2, True)
(I put it in D1 so as to be able to compare what she had already copy
pasted in C)
This worked! So I copy pasted D1 over column D, and voila, done.
I was her hero that day! (saturday)
Except that she has codes with significant leading zeros and letters. This
messed everything up. Fortunately for me she figured out that when importing
the file with 'Text' instead of 'General' format it would fix that
problem (tuesday). And she kept on typing away and copy pasting rows that
two other people fat finger for her.
Somwhere in the process something went amok (it's now thursday: a lot of
typing guys!). Now my formulae spits out #REF! on row 666 and all the
following rows pick up the code from the row above (the first argument to
the VLOOKUP reflects that). Over the phone (she lives 4 hours from where
I'm at), I got her to redo the column with the formulae to no avail.
Has cell A666 vanished into nothingness? Any educated guess as to what is
causing this problem and how to fix it?
Is there a semantic difference between A1 and rc[-3] or is that just
Is my aproach correct because it slows the machine down a lot, if there is
something better I'm taker (not too complicated: she won't do perl!)