grumble.

[valis2]

I was given a task yesterday at the day job, and I pushed through a third of it. Last night I had an "a-ha" moment--I realized I could use the VLOOKUP function in Excel to cut off about a third of the remaining time involved in the project.

This morning I spent an hour and fifteen minutes fiddling with LOOKUP, VLOOKUP, and even INDEX/MATCH and could not get the bastard to work properly. (I have a feeling that it's because I absolutely have no choice about storing numbers as text in the document, and somehow, when it's trying to match them, it's either a) thinking the numbers for the array aren't "sorted" and therefore it balks, or b) the reference to a cell is making the texty-number all sad and useless.)

So I was thirty pages short of finishing. I volunteered to come in tomorrow, thinking that they wouldn't bother with it, but they want me to. Sigh. (If I had just not spent that time trying to work smarter, lol...)

My two options:

1) Go in and work a half day, which (theoretically) leaves me time to do my errands, like going to the post office, working on Etsy/eBay stuff, packing boxes, buying pads, and going to the grocery store. However, then I have to drive in by myself (my sister is working a full day) and that's a big gasoline draw. And I'm so damned tired and sleepy because I've been writing (er, Riptide stuff) and I'm all full of story thoughts and I can't sleep, and my period started today, so I'm even more tired, and I don't want to get up and go in.

2) Go in and work a full day to save on gasoline and make some $. House, after all. But I'm so tired. And then I wouldn't have time for the errands. Poor Husband would have to do the post office stuff and he's already been running ragged this week. Plus, I'm on my period and I don't want to sit there all day during the worst day of my period. wah.

I've decided on 1. Plus, I can run and drop something off to a friend whose store is very near work, and I think she'd be pretty geeked about it.

*yawn*

Comments

[lady-branwyn.livejournal.com]

Do you know about my best friend "text to columns" which will convert the textiness to numberiness? I have certain downloads which barf mightily until I run text to columns.

[valis2.livejournal.com]

The problem is I can't actually change the text to numbers--it would be a disaster. The numbers are kind of like a Dewey decimal system...560 is not the same as 560.0, and if I change them to numbers, it will screw them up. :( Some have one decimal place, some have two, and it's a real nightmare.

The thing is, it should lookup the data anyway! You can do this with text OR numbers. argh.

[lady-branwyn.livejournal.com]

Could there be either leading spaces or control characters in front of some of them? Especially when you have data that someone entered as keystrokes from a POS terminal, you can get that sort of glitch. And it can drive you nuts.
Edit: And the control characters won't necessarily display in Excel.
One thing to experiment with might be to dump the data into something like Notepad, save it as the most vanilla text file possible (to get rid of hidden formatting) and then copy it back to Excel.

[valis2.livejournal.com]

Unfortunately, it was entered directly by my own hands, so I can testify that it doesn't have any extra characters. I have a strong feeling that because the array was sorted by pretending that all numbers are text, that the array is not being read properly by the function--that's where the error shows up. It registers the value, but can't seem to find it in the array.

This bugs me to no end, because I would have loved to cut the time off this job! :(

[lady-branwyn.livejournal.com]

How about telling the lookup to find an exact match regardless of how the data is sorted?

Blah...a bunch of stuff...blah blah

Table_array is the table of information in which data is looked up. Use a reference to a range or a range name, such as Database or List.

If range_lookup is TRUE, the values in the first column of table_array must be placed in ascending order: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise VLOOKUP may not give the correct value. If range_lookup is FALSE, table_array does not need to be sorted.

What if you set the range_lookup to FALSE so the array doesn't need to be sorted?

[valis2.livejournal.com]

The lookup function does not find exact matches, unfortunately. Vlookup is supposed to, and I set it to "false" so it would, but it only finds it if I convert the texty number to number, which isn't possible for the ICD-9 codes. *sigh*

[lady-branwyn.livejournal.com]

One more idea. I am finding that VLOOKUP wants text strings enclosed in quotes or it spits out an error message. So to look up a text 1, you would use the following--

=VLOOKUP("1",X7:Z22,2,FALSE)

Possible that Excel is reading some as numbers and some as text? Maybe reformat entire column as text?

[valis2.livejournal.com]

Unfortunately, I can't do the quote thing because the thing to lookup is a cell reference. It finds the cell reference just fine and will display it when I use the evaluate formula dialogue box. However, once it starts to try to match the texty number that was in the cell, then it can't.

I've tried reformatting the columns, and that didn't seem to work. Excel is definitely reading some as numbers and some as text, and because what it's finding in the reference cell is a texty number, it won't match it with the array, which is half and half, even though I've tried to reformat all of them to text. :(

It's so awesome to talk to another Excel aficionado! ;)