We help IT Professionals succeed at work.

how to mark numbers were sequential numbering is interrupted

Dear Experts:

I got thousands of figures in Column A of the current worksheet. They are sorted and have the following make-up:

90-001
90-002
90-003
90-005
90-007
90-009
90-020
90-021
90-022
90-025
.....
90-437

Is it possible by means of a formula or conditional formatting to show the ones where the sequence of '1' is not maintained,

Help is much appreciated. Thank you very much in advance.

I have attached a sample file for your convenience.

Regards, Andreas
sequential_numbering.xlsx
Comment
Watch Question

Finance Analyst
Commented:
Try this in Conditional Formatting with formula:

=VALUE(RIGHT(A2,3))<>VALUE(RIGHT(A1,3)+1)

See attached
sequential_numbering.xlsx
You can also enter this formula in B3 and then copy down.

=IF(RIGHT(A3,3)-RIGHT(A2,3)-1,"sequence of '1' is interrupted","")
Rob HensonFinance Analyst

Commented:
Realised that don't actually need to enclose in the VALUE function:

=RIGHT(A2,3)<>RIGHT(A1,3)+1
Andreas HermleTeam leader

Author

Commented:
Both codes work just fine. Thank you very much for your great help. I really appreciate it. Regards, Andreas