Items Out Of Sequence.

[Feastie]

Hi,

I have a list of store orders that should be picked in store sequence but unfortunately are not 100%.
I need to summarise the compliance of these orders but am struggling to calculate it correctly.
It is fairly simple to manually look through the list and mark any non-compliant items but I have tens of thousands of rows to go through each day.
I attach a simplified version to help paint the picture.

Thanks, Feastie.

[Kaper]

As a starting point try in D4:

=IF(AND(C4>=MAX(C$3:C3),C4>MIN(C5:C$68)),"x","")

Almost out of 11 manually spotted formula identified 8 (but 1 "extra")

so may be:

=IF(AND(COUNTIF($C$3:$C$68,C4)>1,C3<>C4,C5<>C4),"x","")

10 out of 11 (but here 4 extra)

or may be

=IF(AND(C4>=MAX(C$3:C3),C4>MIN(C5:C$68)),IF(AND(C3<=C4,C4<=C5),"","x"),"")

(7/0)

or:

=IF(AND(COUNTIF($C$3:$C$68,C4)>1,C3<>C4,C5<>C4),IF(AND(C3<=C4,C4<=C5),"","x"),"")

(10/2)

Generally - the rules are not so obviuos for me, because instead of

1	
2	x
1	
2	x
1	
1

one could probably mark

1	
2
1	x	
2	
1	x	
1	x

as it also follows:

Orders should be picked in ascending order, not necessarily sequentially. (e.g. 1,2,5,6 is fine)
Product types (Required Sequence) should be grouped (i.e. all 1s together, all 2s together etc.)

May be I am wrong, but to say it different way: the manual marking is based not on "fully objective rule" but a bit influenced by "perceived grouping of data" and the logics of human perception is not so easy to incorporate in excel formulas.

See attachement - may be this will push things a but further

[Feastie]

Thanks Kaper - I agree that the way I have 'Manually Identified' deviants could be classed as subjective however, there must be an objective rule that can be applied in this instance - that's the part I'm struggling with though. Thanks for your help

[newdoverman]

Maybe a Pivot Table like this would help you out.

[Feastie]

Thanks Newdoverman,

it doesn't give me the answer but it certainly gave me another way of attacking it. This very nicely shows me a grouping by 'Required Sequence' but that could have been obtained by sorting by 'Required Sequence'. None the less, a star is deserved for your help. Thanks.