Way to identify items not in alphabetical order?

[shipindex]

I have a large file in Excel 2007 that's ostensibly in alphabetical order. But because it was OCR'd, errors have occurred, and some entries were mis-read. An example would be:

<table style="border-collapse: collapse; width: 149pt;" border="0" cellpadding="0" cellspacing="0" width="199"><col style="width: 149pt;" width="199"> <tbody><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 149pt;" height="17" width="199">Janna</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Jannlcke</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Jannikke</td> </tr> </tbody></table>
or

<table style="border-collapse: collapse; width: 149pt;" border="0" cellpadding="0" cellspacing="0" width="199"><col style="width: 149pt;" width="199"> <tbody><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 149pt;" height="17" width="199">Jullia Ann</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Jurnna</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Junco</td> </tr> </tbody></table>
If I could know when an entry is not in alphabetical order, such as "Jannlcke" before "Jannikke", I could find many of these errors more easily. Is there a way to do this? Perhaps some form of conditional formatting, or data validation? I have looked in the forums here, and in MS KB, and online generally, without success.

Suggestions would be greatly appreciated. Thanks.

Peter

 

[VoG]

Could you not simply sort the list?

 

[Cindy Ellis]

Extending VoG's suggestion...
You could create another column to store the current sequence (put 1 in the first row, 2 in the second row, higlight both cells, then fill down to the last row of data). This column should represent a correctly-sorted list, had everything scanned correctly. Now sort alphabetically. Your sequence column is now "out of order", but that's what you want right now.
Now add a formula in another empty column, referring to the sequence column. For purposes of example, assuming the sequence column is column A and the new column is column B, put the following formula in B2 and fill down:

=A2-A1

This will put a 1 in any row that's in order, and something other than 1 in each row that's out of order.

Hope this helps,

 

[shipindex]

Thanks for the suggestions.

VoG, I don't just want them sorted in alphabetical order. I want to find the ones that are not in order, so that I can check them because that almost certainly represents an OCR error, and I want to fix that.

Cindy, I thought a bit about the path you recommend, but my concern is that a major error early on, such as a "P" in the first character being read as a "B" (or, worse, vice versa) will make every succeeding entry incorrect, thus negating the value of the solution. Also, I'd really like some conditional formatting tool so that when I make a correction the highlighted field is no longer highlighted -- or, even better, when I make a wrong correction the field becomes highlighted.

 

[VoG]

Perhaps like this

<b>Sheet2</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td style=" border-width:3px; border-styleutset;border-color:#f0f0f0; ">A</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >2</td><td >Jullia Ann</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >3</td><td >Jurnna</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >4</td><td style="background-color:#ff0000; ">Junco</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; background-color:#fffcf9; border-style: groove ;border-color:#ff0000"><tr><td ><b>Conditional formatting </b></td></tr><tr><td ><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial; font-size:10pt; padding-left:2pt; padding-right:2pt; "><tr><td >Cell</td><td >Nr.: / Condition</td><td >Format</td></tr><tr><td >A2</td><td >1. / Formula is =A2<A1</td><td style="background-color:#ff0000; ">Abc</td></tr></table></td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4

 

[shipindex]

Hey, that's really super. That tells me just what I want. I didn't know I could do that that way; it's great.

Thanks a million!