Show any differences in one column where cells match in another column

[seansr]

I have a spreadsheet full of stock items.

Column C contains the Stock Item

Column D contains the Cost Price


There are many duplicate Stock items, but what I want to do is identify any difference in the cost price


So is there a formula I can try that where Stock items match in C show any difference in the Cost Price in D


Thanks in anticipation

 

[NARAYANK991]

Hi Sean ,


Will there be exactly one duplicate , or can the item number repeat more than twice ?


If there is exactly one duplicate , then the difference between one price and the other can be shown , but suppose an item is repeated 4 times , with 4 different prices , then how can the difference be shown ?


Narayan

 

[seansr]

Narayan


In this instance in column C there will be about 10 identical copies of each stock item, and hopefully in column D the cost price should be identical for each row.


What I am wanting to do is show where there is a difference so that we can identify which store the cost price is different and theefore correct it


But on the otherhand theoretically there could only be one entry for the stock item


Hope that helps

 

[Faseeh]

Hi seansr,


What is criteria for the standard price for this case. I mean when you will be taking diff. of price you should have some price as reference to take diff. from. Will it be the max. price or min. price or the first price encountered in the list?? Secondly will you be showing diff. against each similar item in the next coulumn (Col. E)? Please clarify.


Faseeh

 

[seansr]

The spreadsheet is an extract from a SQL report showing all the items sold and the cost price that has been set at each store. As each site operates a contract price the cost price should be idenitical at each site. However there has been human intervention and things have been incorrectly set up at some stores

As there are 10's thousands of rows visual looking is painfull and time consuming


What I want is basically for a formula to show where there is a matching stock item in C show any differences in D


so if not all the cost prices are the same show an alert : "Cost Prices Need Checking" otherwise ""

 

[NARAYANK991]

Hi Sean ,


You can use this formula :


=IFERROR(STDEV(IF(C2=Stock_data,Price_data)),0)<>0


entered as an array formula , using CTRL SHIFT ENTER.


Stock_data is a named range which has the item codes in column C ; Price_data is a named range which has the item prices.


If all the prices for the several duplicates of an item code are the same , the result will be FALSE ; if there is even one price which is different , the result will be TRUE.


Enter this formula in E2 , and copy down.


Narayan

 

[NARAYANK991]

Hi Sean ,


This is not related to your topic , but I think you will find it interesting , since it concerns an earlier topic of yours relating to pre-1900 dates.


http://www.linkedin.com/groups/Finding-weekday-from-given-date-3843467.S.164105099?view=&gid=3843467&type=member&item=164105099&trk=eml-anet_dig-b_nd-pst_ttle-cn


Narayan

 

[seansr]

I Tried your formular but it came back as True on every field. Below is a link to a copy in my dropbox


https://www.dropbox.com/s/tt6d97rpmi91ych/Check%20Cost%20Price.xlsx


Sean

 

[NARAYANK991]

Hi Sean ,


I have mentioned that this is to be entered as an array formula , using CTRL SHIFT ENTER.


This has not been done.


Can you please do that , and then get back to me ?


Narayan

 

[seansr]

Sorry, I missed that step

Not something I have used before

So I have re-enterd the formula and the pressed the CTRL SHIFT ENTER which has put the {} arround it

It has changed the outcome but is showing a lot of wrong results. Where some are identical they are showing False


Have I still do do something else


Due to the size of the file I have stopped it calculating automatically


Sean


By the way thanks for your othjer post re: pre 1900 dates - much appriciated

 

[NARAYANK991]

Hi Sean ,


Sorry about the problem , but the point is that even when all the prices are the same , the STDEV function does not return exactly zero , but a very small value. Change your formula to :


=IFERROR(STDEV(IF(C2=Stock_Data,Price_Data)),0)<0.002


Enter this as an array formula , and copy it down.


Since I am checking for less than 0.002 , if all the prices for the several duplicates of an item code are the same , the result will be TRUE ; if there is even one price which is different , the result will be FALSE.


Narayan