Quick way to find a broken sequence of units ordered

[Michael Govea]

Hello!

The attached, shows size breaks purchased per account for each style and color
(sizes 4,6,8,10,12,14)

Each account was to order a 12 unit case pack and must follow the 1-2-3-3-2-1 size break for each style & color.
Meaning, they account must order 1 unit-size 4, 2 units-size 6, 3 units-size 8, 3 units-size 10, 2 units-size 12, 1 unit-size 14.

Is there a quick way to only show who and what style/color (or perhaps, highlight in red) that didn't follow the mandatory 1-2-3-3-2-1 size run?

As a quick example, I highlighted each style/color in red that didn't order 1-2-3-3-2-1 for the first few accounts.

FYI - an account can order a style/color case pack more than once, meaning if an account ordered style F5555555 Boot in Black twice, their total units should be 24 and size run must have a 2-4-6-6-4-2 size run and so forth. Also, an account may order 12 units of a style but the 1-2-3-3-2-1 size run cannot be broken.

Please let me know if I can further clarify!

Thanks you!!!!!!!!! I will forever be indebted to you!

Michael

 

[NARAYANK991]

Hi Michael ,

Can you try this array formula in any unused column , say column P , in cell P2 ?

=ISNUMBER(MATCH($I2:$N2/$I2,{1,2,3,3,2,1},0))

This will show TRUE where the data in a row is in the required proportion , and FALSE otherwise.

Enter this in P2 , using CTRL SHIFT ENTER , and copy down.

Narayan

 

[Michael Govea]

Hi Michael ,

Can you try this array formula in any unused column , say column P , in cell P2 ?

=ISNUMBER(MATCH($I2:$N2/$I2,{1,2,3,3,2,1},0))

This will show TRUE where the data in a row is in the required proportion , and FALSE otherwise.

Enter this in P2 , using CTRL SHIFT ENTER , and copy down.

Narayan

Hi Narayan,

Thank you for this - I feel like we're on the right track, however, everything shows up as FALSE, when I drag down. Any thoughts as to why this is?

 

[rush2rajen]

Hi Michael,

Please check this. Its an array formula. Entered the same in P2 , using CTRL SHIFT ENTER , and copied down.

Thanks
Raj