etherealfocus
Senior member
Sorry if this doesn't count as programming - didn't know where else to post. Feel free to move this if needed, mods. 🙂
Anyway, here's the problem: I've got two Excel spreadsheets. One contains a list of 30,000 products with SKU, price, MAP (minimum advertising price), and some other data. I compiled the spreadsheet by downloading a dozen different reports from our web store back end - one report for each major product area, since the report generation fails if I try to get all 30k product records at once.
Many products are in multiple categories, so there are a lot of duplicates in my final product sheet.
The other spreadsheet is our upload form for a third party site we also sell on, and it has a clean list of UPCs that I got directly from our supplier's web service. Basically, our web store has a direct feed from our suppliers, and we take data from that site and crunch it to fit the needs of our other outlets. The data we get from our web store does not include UPCs, which we have to get directly from the supplier.
My sad task today is to merge these two sheets: long story short, I have UPCs and SKUs on my upload sheet and SKUs and MAPs on my product sheet.
I know how to make it happen: create a column in my upload sheet called MAP and fill it with VLOOKUP statements that match SKUs on the upload sheet to SKUs on the product sheets, then get the corresponding MAP. That should in theory avoid all the issues with duplicate records in the product sheet and avoid a messy process of filtering out duplicates, then sorting columns to match and copy-pasting.
Unfortunately, I just kinda suck at VLOOKUPs. I'm pretty decent at Excel, but never needed to use a VLOOKUP before now.
I've been reading the below articles for help:
http://www.howtogeek.com/howto/13780/using-vlookup-in-excel/
http://office.microsoft.com/en-us/excel-help/vlookup-HP005209335.aspx
I think the problem is probably just some stupid syntax goof, but it's late and I'm a bit fried and honestly I wouldn't be surprised if I just failed to grok the stupid instructions. 😛
In my upload sheet, the SKU is in column A and the MAP field with the VLOOKUPs is in column B.
In my product sheet, the SKU is still column A and I moved the actual MAPs to column B for simplicity. Essentially, the VLOOKUP formula needs to get the value from an adjacent cell, lookup that value in the A column of the other sheet, and grab the value in the corresponding cell in the B column.
Maybe the problem is that it's essentially expecting a primary key field with unique values, but the values aren't unique in the product sheet because I have duplicates?
...I hate to ask because I expect the answer is yes, but is there an easier way to do this? Please just don't tell me I've been grinding the wrong function this entire time. 😛
Anyway, here's the problem: I've got two Excel spreadsheets. One contains a list of 30,000 products with SKU, price, MAP (minimum advertising price), and some other data. I compiled the spreadsheet by downloading a dozen different reports from our web store back end - one report for each major product area, since the report generation fails if I try to get all 30k product records at once.
Many products are in multiple categories, so there are a lot of duplicates in my final product sheet.
The other spreadsheet is our upload form for a third party site we also sell on, and it has a clean list of UPCs that I got directly from our supplier's web service. Basically, our web store has a direct feed from our suppliers, and we take data from that site and crunch it to fit the needs of our other outlets. The data we get from our web store does not include UPCs, which we have to get directly from the supplier.
My sad task today is to merge these two sheets: long story short, I have UPCs and SKUs on my upload sheet and SKUs and MAPs on my product sheet.
I know how to make it happen: create a column in my upload sheet called MAP and fill it with VLOOKUP statements that match SKUs on the upload sheet to SKUs on the product sheets, then get the corresponding MAP. That should in theory avoid all the issues with duplicate records in the product sheet and avoid a messy process of filtering out duplicates, then sorting columns to match and copy-pasting.
Unfortunately, I just kinda suck at VLOOKUPs. I'm pretty decent at Excel, but never needed to use a VLOOKUP before now.
I've been reading the below articles for help:
http://www.howtogeek.com/howto/13780/using-vlookup-in-excel/
http://office.microsoft.com/en-us/excel-help/vlookup-HP005209335.aspx
I think the problem is probably just some stupid syntax goof, but it's late and I'm a bit fried and honestly I wouldn't be surprised if I just failed to grok the stupid instructions. 😛
In my upload sheet, the SKU is in column A and the MAP field with the VLOOKUPs is in column B.
In my product sheet, the SKU is still column A and I moved the actual MAPs to column B for simplicity. Essentially, the VLOOKUP formula needs to get the value from an adjacent cell, lookup that value in the A column of the other sheet, and grab the value in the corresponding cell in the B column.
Maybe the problem is that it's essentially expecting a primary key field with unique values, but the values aren't unique in the product sheet because I have duplicates?
...I hate to ask because I expect the answer is yes, but is there an easier way to do this? Please just don't tell me I've been grinding the wrong function this entire time. 😛
