View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Walter Briscoe Walter Briscoe is offline
external usenet poster
 
Posts: 279
Default Finding missorted cell in range

I have a worksheet consisting of about 400 rows, with about 80 columns
in each row.
The data in column(1) should be sorted and is so.
I just found data in column(62), which ought to be sorted, but is not.

I decided to look for an array formula to hit the spot.

I simplified the problem to playing card suits.
I want an array formula which finds the first unsorted item.

I have

A B C
1 Suits Random Random
2 Club Club Club
3 Diamond Heart Heart
4 Heart Diamond Diamond
5 Spade Spade Spade
6 Sort Shuffle Diamond

A5 is [=IF(AND(A$2:A$4<=A$3:A$5),"Sort","Shuffle")]
B5 is [=IF(AND(B$2:B$4<=B$3:B$5),"Sort","Shuffle")]
C5 is beyond my limited skill.

While I suspect Conditional Formatting ought to be usable to flag
unsorted cells, putting Cell Value is greater than C2 in C3 actually
compares the value in C3 with the constant "C2".

In principle, I should be able to add a "NoTrump" row, and have formulae
which continue to work, but can't currently think how. ;)
--
Walter Briscoe