How can I compare two range of data and view percentage difference

[Richard Michael Duffy]

Hi,

I want to look at our shop vs website sales and see if we have any products that sell in store and not online. I've seperated the sales into two different sheets (one shop, one web) with the same format as pasted below. Can I use a formula to compare the volume of products that appear in both lists and give a percentage difference between the two that I can thin filter on?

Below is an example of the data I am using. I have the data in individual worksheets at present but can put them both in one if necessary.

[TABLE="width: 287"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]TOTAL[/TD]
[TD]ITEMDESC[/TD]
[/TR]
[TR]
[TD]345[/TD]
[TD]EXPLORER MAP[/TD]
[/TR]
[TR]
[TD]322[/TD]
[TD]LMF SPORK[/TD]
[/TR]
[TR]
[TD]248[/TD]
[TD]SUPERFEET ACTIVE GREEN[/TD]
[/TR]
[TR]
[TD]223[/TD]
[TD]SUMMIT MID HIKING[/TD]
[/TR]
[TR]
[TD]184[/TD]
[TD]LANDRANGER MAP[/TD]
[/TR]
[TR]
[TD]147[/TD]
[TD]2MM CORD ON REEL P/M[/TD]
[/TR]
[TR]
[TD]122[/TD]
[TD]SUMMIT LIGHT HIKING[/TD]
[/TR]
[TR]
[TD]114[/TD]
[TD]5MM CORD ON REEL P/M[/TD]
[/TR]
[TR]
[TD]113[/TD]
[TD]SUMMIT MID HIKING WMS[/TD]
[/TR]
</tbody>[/TABLE]

Thank you very much for your help,
Richard

 

[stelladave]

Richard,

Are the shop and web lists identical in terms of ITEMDESC? If the same items are included on both lists then the easiest way to compare would be to sort both lists alphabetically by ITEMDESC, and copy and paste one of the totals next to the other totals on the same sheet. The list must be identical though or you will not have correct totals for each product. Once the copy and paste is done, simply use the formula below:

a b c
ITEMDESC WEB Total Shop Total

1 LMF SPORK 322 345

=(c1-b1)/c1

this will return a value of 7% and will show you +/- % difference of shop over web sales. To reverse the % just switch the cell references.

 

[Richard Michael Duffy]

Hi Dave,

Thank you very much for helping me.

The columns are not identical, the titles are the same but there will be more products from the shop than the web.

Thanks,
Rich.

 

[stelladave]

Rich,

Okay, that's not a problem, it just takes an extra step and the use of a function called vlookup. The vlookup function is going to reference the web product totals using the same titles in the shop product totals. Here's what you need to do.

On the sheet that has the shop data, move the Item Desc data to column A, and the corresponding shop totals data to column B. I will assume the data begins on row 2 (row 1 is for the column titles). column c will be the web totals data that we will import from the web totals sheet.

Apply the same layout to the sheet that has the web totals. Now, on the shop totals sheet, in cell C2, enter the following formula:

=vlookup(A2,Web_totals!$A$2:$B$50,2,FALSE)

A2 = the itemdesc value on the shop totals sheet

Web_totals!$A$2:$B$50 = the hypothetical name and range of the web total sheet. After entering A2 and a comma you can move to the web totals sheet and select the data set - this will automatically complete this part of the formula. The cells being referenced must be locked (a $ sign before each cell component accomplishes that) in order for the formula to work.

2 = the second column in the referenced data, i.e. the web totals data

False = we will require an exact match of the itemdesc from the shop totals sheet to the web totals sheet.

Drag this formula down for all itemdesc data points and you should have the web totals next to the shop totals for the same itemdesc. Then, apply the same formula I mentioned earlier to look at % differences.

Dave

 

[Richard Michael Duffy]

Hey Dave,

Please excuse the slow response. I've been working on something different the past few days and only just been able to jump back on this today.

The lookup code you gave me worked a treat, I've had to have a few goes as I realised I'd need more data than I originally listed but have been able to get everything sorted.

Thank you very much for all your help with this it is much appreciated. I've been able to use lookup codes for a couple of other things I'm working on today too which is good.

Thanks again,
Rich.

 

[stelladave]

Glad to hear it Rich, you're very welcome.

Dave