My landlords approached me and asked if I knew anything about Excel. My initial thoughts were “I know how to add and subtract with =SUM() and that’s about it”. But I thought, how hard can it be?
They tasked me to sort their product list into one file so all their products had the appropriate product codes, UPC codes, descriptions, cost & sell price and physical attributes (length/width/height/weight). The idea was to then import the list into their Magento database.
The task unravelled to be quite difficult as the MasterSheet had 7000 items of unique product codes, descriptions, cost and sell prices. I needed the products’ physical values too. So I was given spreadsheets that was a combined list of over 50,000 items that their suppliers currently sell, including each products’ physical values. I needed a way to accurately and quickly search through 50,000+ items, 7000 times.
The key factor that enabled me to achieve this was the fact that the MasterSheet and all the supplier sheets had one common dataset; the unique product code. Below is an illustration of what I had:
At this point, I pretty much did what everyone does when they’re stuck… Some kind sole told me to look into vlookup. vlookup is a tool to hunt and extract data from within the same worksheet or another worksheet (or workbook!). Below is a brief run down of the function in a similar fashion to what I did for the Linux /etc/passwd file.
- The unique value: tell Excel the cell location of the common dataset between the tables. In my case this was the ‘Product code’ column, as this dataset was shared between the MasterSheet, and SupplierSheet1 & SupplierSheet2.
- The table of data to search: tell Excel the coordinates of the table that you want it to search. Here is also where you state whether the table is within the same worksheet, a different one, or a different workbook. The above shows that the table I want to search is within the same worksheet, and it’s coordinates are L2 to P22. The dollar symbols are used to ensure that the table’s position is fixed and does not move when applying this function to other cells by using the fill handle. Is the table you want to search in a different worksheet or workbook?
- The column to extract data from: tell Excel which column from the given table array to extract data from. For example, if I wanted to return the corresponding Length value from either SupplierSheet1 or SupplierSheet2, then I would set this value to be 2. If I wanted the width, I would set it to be 3. If I wanted height, the value will be 4. If I wanted weight, it would be 5.
- Absolute match, or relative match? Typically, you would set this value to be FALSE to yield an exact match. However, if you were searching for something like filenames where only subtle differences would exist, then you could set the value to be TRUE. I haven’t exercised the use of this parameter with a TRUE value yet and I can’t see a practical reason as to why you would.
Instead of using $L$2:$P$22, you must pre-append the table’s coordinates with the worksheet’s name followed by an exclamation mark. Like so, SupplierSheet1!$L$2:$P$22.
Same the above, instead of using $L$2:$P$22 you must pre-append the table’s coordinates with the workbook’s absolute location, enclosed in single quotation marks and the filename enclosed in square brackets. Like so, ‘C:\Path\To\Directory\[Workbook.xls]SupplierSheet1’!$L$2:$P$22.
My working example is below, where I am searching through my Suppliers worksheet from my Products spreadsheet:
I know my current work with Excel is not elegant and it has lots to polish up on (for example, I could make use of Excel tables), but given my recent experience I will be more than happy to undertake another project or requirement with it and learn more. I’ve recently signed up for one of the courses on Lynda for Excel training, of which I’m very excited about.