Mar. 29th, 2007

caffeine: (PSP)
Sadly, nothing as cool as a TARDIS. 

But, I did learn about a function in MS Excel today that would have saved me much time had I known about it a few months ago.  When you get up from falling over because I said something positive about Microsoft, especially MS Office, you can read about it.

You can use VLOOKUP or HLOOKUP to reference data based on the contents of matching cells (somewhat like a SQL query).  So if you have a sorted list on one tab, and an unsorted set of the same things on another, rather than painstakingly copy and pasting from tab to tab, you use these functions to copy in the right data.

It is important since I get a dataset every week where the objects (SAN switches in this case) are in a different order.  Now I can say look at the rows in column B (switch name), when you find the same name in a row in column X on Sheet2, put the value from column EE for that row in this cell.  We used absolute references ($B$2:$EE$99) so we could make use of the Copy down function to populate the other cells of this column.  Yes, there are really over 135 columns in the source data.  Not all of them are used and the designer of the spreadsheet really liked merged columns.  Also, they either got paid by line of code, or by how convoluted the code looked.

Profile

caffeine: (Default)
caffeine

October 2022

S M T W T F S
      1
2345678
9101112131415
16171819202122
23242526272829
3031     

Most Popular Tags

Page Summary

Style Credit

Expand Cut Tags

No cut tags
Page generated Jun. 11th, 2025 09:17 pm
Powered by Dreamwidth Studios