Compare pricelists in excel 2007
This post describes how to compare two price lists in excel 2007. I am going to create three conditional formatting formulas and highlight:
- new products yellow
- prices higher than previous year green
- prices lower than previous year red
Sheet1 - Year 2011
Sheet2 - Year 2010
Create named ranges
You can´t reference cells outside current sheet when constructing a conditional formatting formula unless you use named ranges.
category2010 - B3:B12
product2010 - C3:C12
price2010 - D3:D12
- Select sheet2
- Select B3:B12
- Type category2010 in name box
- Repeat step 1-3 with remaining names and cell ranges
Highlight new products yellow
Conditional formatting formula:
Highlight higher prices green
Conditional formatting formula:
Highlight lower prices red
Conditional formatting formula:
How to apply conditional formatting formula
- Select sheet1
- Select cell range B3:D14
- Click "Home" tab
- Click "Conditional Formatting" button
- Click "New Rule.."
- Click "Use a formula to determine which cells to format"
- Copy and paste conditional formatting formula in "Format values where this formula is TRUE" window.
- Click "Format.." button
- Click "Fill" tab
- Select a color for highlighted cells.
- Click "Ok"
- Click "Ok"
- Click "Ok"
Download excel file
Compare pricelists_new.xlsx
(Excel 2007 Workbook *.xlsx)
Recommended blog posts
How to quickly compare pricelists in excel
Functions in this article:
INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range
MATCH(lookup_value;lookup_array; [match_type]
Returns the relative position of an item in an array that matches a specified value
COUNTIFS(criteria_range1,criteria1, criteria_range2, criteria2...)
Counts the number of cells specified by a given set of conditions or criteria
Related posts:
How to quickly compare pricelists in excel
Compare two lists of data: Highlight records existing in only one list in excel
Compare two lists of data: Highlight common records in excel
Highlight duplicate rows in excel 2007
Highlight odd/even months using conditional formatting in excel 2007
Recommended categories
This is exactly what I was looking for.
Unfortunately it does not work in my specific case and after working a few hours I am kinda stuck...
The only differences are the following:
1. I do not have a product column
2. my category and prices column are not adjacent (B and E)
3. I've multiple prices column (month, E, F, G, etc.)
I am trying to compare monthly prices from year 1 (worksheet 1) with monthly prices from year 2 (worksheet 2).
Any brillant ideas would be very much appreciated
So far this is what my latest try:
=$E2>INDEX(price2010, MATCH(1, COUNTIF($B2, category2010), COUNTIF(E$1, month2010)))
Also tried this one:
=E2>INDEX(price2010, MATCH(1, COUNTIF($B2, category2010), MATCH(1, COUNTIF(E$1, month2010))))
price2010 - E2:P20
category2010 - B2:B20
month2010 - E1:P1
I was also expecting a MATCH(0,...) and not a MATCH(1,...)
Now I've reached my technical abilities
Thank you for your help in advance
Géraud,
1. I do not have a product column
2. my category and prices column are not adjacent (B and E)
3. I've multiple prices column (month, E, F, G, etc.)
I need to know what columns to compare?
- Compare prices in columns E and F, F and G, G and H etc. on sheet1.
or
- Compare prices in column E on sheet1 and column E on sheet2 and so on..
Dear Oscar,
Thank you for the quick reply.
Indeed I would like to compare prices in column E on sheet1 and column E on sheet2 and so on.
Ideally I would like to compare the prices in each cell of range E2:P20 on sheet 1 with the prices in each cell of range E2:P20 on sheet 2.
I am pretty sure it will work column by column but I guess the INDEX formula should work on both rows and columns?
Cheers, Géraud
Géraud,
Your scenario is a bit different. I´ll try to explain the formula.
Named ranges:
data_2010 - Sheet: 2010 Range: E2:G5
headers_2010 - Sheet: 2010 Range: E1:G1
category_2010 - Sheet: 2010 Range: B2:B5
Highlight higher prices green
Highlight lower prices red
Explaining conditional formatting formula applied to cell E2
The formula compares the value on sheet 2011 (bolded) with the "correct" value on sheet 2010.
To find the "correct" value on sheet 2010 the formula matches the header on sheet 2011 (bolded) with the headers on sheet 2010 (bolded) and returns it´s relative position.
MATCH(E$1, headers_2010,0)
becomes
MATCH("Feb", {"Jan", "Feb", "March"}, 0) and returns 2.
It also matches the category (bolded) and returns it´s relative position.
MATCH($B2, category_2010, 0)
becomes
MATCH("C", {"A";"B";"C";"D"}, 0) and returns 3.
=E2>INDEX(data_2010, MATCH(E$1, headers_2010, 0), MATCH($B2, category_2010, 0))
becomes
=303>INDEX(data_2010, 2, 3)
becomes
=303>302 and returns TRUE.
Download excel file
Compare pricelists_geraud.xlsx
Excel 2007 *.xlsx
Recommended blog post:
How to quickly compare pricelists in excel
Hi Oscar,
My last botched attempt was
E2>INDEX(data_2010, MATCH(1, COUNTIF($B2, category_2010), MATCH(1, COUNTIF(E$1, headers_2010))))
but yours is beautiful in its simplicity by removing the countif. I've just swapped between headers_2010 and category_2010 since it's the category that fixes the row # of the INDEX.
=E2>INDEX(data_2010, MATCH($B2, category_2010, 0), MATCH(E$1, headers_2010, 0))
Your formula is brillant and works like a charm now. It's nice when you can automate these kind of customized functions. Takes time at the beginning but saves much time afterwards!
Cheers, Géraud