Check for inconsistent values - example: one part number: multiple prices, other values

[Brusky]

hi,
I am looking for a way to identify errors in pricing. The error occurs where multiple products use the same part sku. In some rows the data the price and other values are different for the part sku for these items.
Below is an example for toner cartridges that can be used for more than one printer model. The corresponding values are supposed to be the same for each unique part number, but some are different. The mismatching-incorrect prices and yields for the same part number are in red.


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Cartridge[/TD]
[TD]Part Number[/TD]
[TD]Price[/TD]
[TD]Black Yield[/TD]
[TD]Color Yield[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Black Toner[/TD]
[TD]12345B[/TD]
[TD]60[/TD]
[TD]40000[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Black Toner[/TD]
[TD]12345B[/TD]
[TD]60[/TD]
[TD]40000[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Black Toner[/TD]
[TD]12345B[/TD]
[TD]56[/TD]
[TD]4000[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Black Toner[/TD]
[TD]54321B[/TD]
[TD]40[/TD]
[TD]30000[/TD]
[TD]30000[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Cyan Toner[/TD]
[TD]54321C[/TD]
[TD]80[/TD]
[TD]0[/TD]
[TD]25000[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Magenta Toner[/TD]
[TD]54321M[/TD]
[TD]80[/TD]
[TD]0[/TD]
[TD]25000[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Yellow Toner[/TD]
[TD]54321Y[/TD]
[TD]80[/TD]
[TD]0[/TD]
[TD]25000[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Black Toner[/TD]
[TD]54321B[/TD]
[TD]40[/TD]
[TD]30000[/TD]
[TD]30000[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Cyan Toner[/TD]
[TD]54321C[/TD]
[TD]80[/TD]
[TD]0[/TD]
[TD]25000[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Magenta Toner[/TD]
[TD]54321M[/TD]
[TD]80[/TD]
[TD]0[/TD]
[TD]25000[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Yellow Toner[/TD]
[TD]54321Y[/TD]
[TD]80[/TD]
[TD]0[/TD]
[TD]25000[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Black Toner[/TD]
[TD]54321B[/TD]
[TD]37.50[/TD]
[TD]30000[/TD]
[TD]30000[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]Cyan Toner[/TD]
[TD]54321C[/TD]
[TD]88[/TD]
[TD]0[/TD]
[TD]24000[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]Magenta Toner[/TD]
[TD]54321M[/TD]
[TD]88[/TD]
[TD]0[/TD]
[TD]24000[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]Yellow Toner[/TD]
[TD]54321Y[/TD]
[TD]88[/TD]
[TD]0[/TD]
[TD]24000[/TD]
[/TR]
</tbody>[/TABLE]


I need to able to find these type of errors for multiple columns, for example prices and yields and other values.
What formula, function would best identify mismatching corresponding values (price, etc) where they need to be the same value for a their part number?

 

[Aladin Akyurek]

Re: How to check for inconsistent values - example: one part number: multiple prices, other values

Book1
	A	B	C	D	E	F
1	Cartridge	Part Number	Price	Black Yield	Color Yield	consistent?
2	Black Toner	12345B	60	40000	0
3	Black Toner	12345B	60	40000	0
4	Black Toner	12345B	56	4000	0	should be: 60|40000|0
5	Black Toner	54321B	40	30000	30000
6	Cyan Toner	54321C	80	0	25000
7	Magenta Toner	54321M	80	0	25000
8	Yellow Toner	54321Y	80	0	25000
9	Black Toner	54321B	40	30000	30000
10	Cyan Toner	54321C	80	0	25000
11	Magenta Toner	54321M	80	0	25000
12	Yellow Toner	54321Y	80	0	25000
13	Black Toner	54321B	37.5	30000	30000	should be: 40|30000|30000
14	Cyan Toner	54321C	88	0	24000	should be: 80|0|25000
15	Magenta Toner	54321M	88	0	24000	should be: 80|0|25000
16	Yellow Toner	54321Y	88	0	24000	should be: 80|0|25000
Sheet1

In F2 control+shift+enter, not just enter, and copy down:

=IF(COUNTIFS($B$2:B2,B2)=1,"",IF(ISNA(MATCH($B2&"|"&$C2&"|"&$D2&"|"&$E2,$B$1:B1&"|"&$C$1:C1&"|"&$D$1:D1&"|"&$E$1:E1,0)),"should be: "&INDEX($C$1:C1&"|"&$D$1:D1&"|"&$E$1:E1,MATCH($B2,$B$1:B1,0)),""))

 

[Brusky]

Re: How to check for inconsistent values - example: one part number: multiple prices, other values

this is great, thanks!!

 

[Aladin Akyurek]

Re: How to check for inconsistent values - example: one part number: multiple prices, other values

this is great, thanks!!

You are welcome.