Matching two spreadsheets with one common value.

Luckily for you I am the sysadmin and other roles for a small company that deals with retail marketing, so I handle this kind of problem frequently. Specifically making sheets that automatically calculate or re-format for this kind of data.

For this you won't need Index and Match luckily because your Sku's are unique. This is assuming that your master database doesn't have the same Sku number in two different product groups and uses the product group name along with the Sku number. If this is the case, let me know.

First up, Do you know how to do a VLOOKUP? If no, just ask Also: The TEXT function might be a part of the solution as suggested by /u/feirnt. This is an aarea I am not so good with. You may simply be able to make the format text for a particular column in your results sheet and it will convert when it references your input sheet.

  1. Make a blank sheet and call it "Input Sheet - POS".
  2. Make another sheet and call it "Input Sheet - Itemlist"
  3. Make a third sheet called "Matching Sheet"
  4. Paste your relevant data into the input sheets using right click > paste special > values. When using this template again in the future, use the right click > clear data option to clear it without causing problems to formulas. However, I would just save each use as a new sheet and never save the template with data in it; but you will need to clear the data first time after completing testing.

Now Build your matching sheet.

  1. Create some headings that you would like for the items, and under each one create an =[cell] ie =A2 to copy data over from your item-list sheet.
  2. Create the headings you want next to the previous ones for the matching data you want. Lets jsut say it's the description becasue you want to find where descriptions need to be updated.
  3. Do a vlookup under each heading and drag the formula down. Simply reference the Sku in your formula and it will find that Sku in the other sheet. Remember to use absolute references if required, though I don't think of any you would need here. If you need held with Absolute references, just ask.
  4. Build in the text formula if appropriate, or format the right columns to be text.

You should now end up with and item list, with making descriptions from the POS list against each one. You could even create conditional formatting to highlight ones that are different to help show you what needs to be updated.

If you can provide some test data and an example of your end result when doing it manually, around 20-60 lines is easily enough to play with, and you could create some fake data seeing as you are potentially dealing with sensitive info.

/r/excel Thread