A way to compare price lists in Excel?

December 27, 2017 2:57 PM

We get price updates from a couple of vendors who are not fully integrated with our POS sytem. I get the files as an XLS or a CSV that are basically setup so Column A is SKU#, Column B is Price. I need a way to compare a new list to an old list and see which prices have changed.

The hitch is that, quarter by quarter, new products are added and old products are dropped, so row by row the SKUs do not line up from one quarter's price file to the next. My old file might look like:
ABC123 $4.25
DEF378 $5.50
GHI222 $5.55

And the new file might be:
DEF378 $5.59
GHI222 $5.55
JLK123 $4.99

So trying to directly compare column B1 from the old file ($4.25 from ABC123) to column B1 from the new file ($5.59 from DEF378) is meaningless.

Here's an example CSV file. The two SKU columns are two different lengths and contain different SKUs. Is there a way to have set up a formula in say column G that would go "Okay, look at the SKU in D2, and find its match in column A. Compare their respective prices and report if there was a change. Now, proceed to the SKU in D3, and do the same, working through all of Column D."

For the life of me, I can't figure out how to get Excel to do this matching, while still being able to compare the prices in adjacent cells. Is this even possible?

posted by xedrik to Computers & Internet (7 answers total)

I would do a vlookup from one sheet to bring in the prices from the other, and then use the two price columns to do a calculation in an additional column.

People who are really good at Excel could probably do that in a single step without having the vlookup separate from the calc, but if you're going to teach yourself a vlookup right now via Google (which you should, and which I do about 4 times a year because I can't remember in between times) it's easier to do in two hops.
posted by Lyn Never at 3:05 PM on December 27, 2017 [6 favorites]

I have done this sort of thing in other programs using a matched merge feature (combine two datasets on a matched ID which in your case is the SKU) -- you then have Column A = SKU, Column B = oldPrice, Column C = newPrice.

The closest I can come in Excel is Power Query?
posted by basalganglia at 3:06 PM on December 27, 2017

Vlookup is your friend here. I would nest the vlookup formula inside an IF formula to accomplish what Lyn Never is talking about in one step, but if your are learning I think her approach is good.
posted by jeoc at 3:12 PM on December 27, 2017 [2 favorites]

Vlookup will require you to have a range that starts with the SKU and has the price in the next column, so move the SKU column to the beginning on the new sheet. If you want to find the new records on the new sheet, you can use a COUNTIF formula.
posted by soelo at 3:18 PM on December 27, 2017 [1 favorite]

Best answer: In the example csv you provide, put this formula in cell G2:

IFERROR($E2-VLOOKUP($D2, $A$2:$B$16, 2, 0), "New Product")

and copy it all the way down through cells G3 to G24.

Now, in column G, you'll see the difference between the new price and the old price (positive values means the price went up, zero means the price didn't change) or the string "New Product" if this sku doesn't appear in the old list.
posted by mhum at 3:31 PM on December 27, 2017 [3 favorites]

In the CSV there is a duplicate SKU in column A: (AAG10179) with two different "old" prices. That could be a complicating factor...
posted by jim in austin at 4:03 PM on December 27, 2017

Response by poster: Oh my gosh, thanks! VLOOKUP is the magic I needed. Thank you mhum for the pasteable example, that's just what I needed to make it click. Y'all are awesome!
posted by xedrik at 5:55 PM on December 27, 2017 [2 favorites]