Condition of a condition formatting

7ftbill

New Member
Joined
Oct 16, 2020
Messages
3
Office Version
  1. 2010
Platform
  1. Windows
Hey guys, new to the forums.
I have comparisons that I need to do, that I've been going through manually for thousands of lines of data - I need to speed this process up.

My format is this:
STORE / SKU / QTY

Each store will have multiple entries for all of their stock, with MULTIPLE entries of SKUs and QTY's, example (ALL stores are on the same sheet):
STORE A / 123 / 5
STORE A / 123 / -3
STORE A / 125 / 3
STORE A /125 / -3
etc...
There are roughly 60 stores that might have a positive and negative (of the same sku) QTY based off different days of inventory counts - the day of the count does not matter to me as stores find and miss counts of stock all the time.
BUT, I need a way of seeing a MATCH of QTY, SKU, and STORE easily AND/OR a NON match of QTY, SKU, and STORE.

In my head I was thinking a conditional formatting option to at least highlight the match of SKU per STORE and then I can manually look to see if the values are the same or different, but if I can add a third option onto that to see if the values match or are different then that would be the cat's meow.


Thanks in advance for any assistance!
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,667
Office Version
  1. 365
Platform
  1. MacOS
a countifs() should work
Book1
ABC
1STORE A 1235
2STORE A 123-3
3STORE A 1253
4STORE A 125-3
5STORE A 1235
6STORE A 1253
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A:CExpression=COUNTIFS($A:$A,$A1,$B:$B,$B1,$C:$C,$C1)>1textNO
 

7ftbill

New Member
Joined
Oct 16, 2020
Messages
3
Office Version
  1. 2010
Platform
  1. Windows
a countifs() should work
Book1
ABC
1STORE A 1235
2STORE A 123-3
3STORE A 1253
4STORE A 125-3
5STORE A 1235
6STORE A 1253
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A:CExpression=COUNTIFS($A:$A,$A1,$B:$B,$B1,$C:$C,$C1)>1textNO

Thanks etaf for the reply. I think this may not work in my situation, as I need to see both rows highlighted of the matched sku.
I will restate my needs in a different way:
* I would love to see the following outcome - PER STORE > Matched SKU "highlighted" (easiest option)
* Even better would be this: PER STORE > Matched SKU > Matched QTY (meaning 3 positive and -3 negative is a "match") "highlighted in green" > Miss matched (meaning still positive or negative left over) QTY "highlighted in yellow"

I will give you a more robust example of my data:
STORESKUQuantity
Store 1610214666376
-1​
Store 1610214666376
-1​
Store 1610214666420
3​
Store 1610214666420
-1​
Store 1610214666505
2​
Store 1610214666505
-2​
Store 2660543432319
3​
Store 2660543432319
-3​
Store 2660543511304
4​
Store 2660543511373
-3​
Store 2660543515081
3​
Store 2660543515081
-3​
Store 2660543515081
1​
Store 2660543515081
-1​
Store 3660543516675
1​
Store 3660543516675
-1​
Store 3660543517320
9​
Store 3660543517320
-10​
Store 3660543527220
1​
Store 3715802842764
1​
Store 3715802842764
-1​
Store 3715802843075
1​
Store 3715802843563
1​
 

Watch MrExcel Video

Forum statistics

Threads
1,129,689
Messages
5,637,837
Members
416,985
Latest member
mrindira

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top