Finding missing numbers in a sorted sequence

[Liz]

Hi there

We use Excel to create our orders and our order numbers start with TT- (Eg:
TT-4056). We are at 7000 entries and I need a way to check that no orders
have been left out.

How do I highlight any missing numbers in the sequence of order numbers?
Eg:
TT-4055
TT-4056
TT-4058
TT-4059
I need to know that TT-4057 is missing.

Any help appreciated!!!

 

[DILipandey]

Hi Liz,

I am assuming that you have the data which needs to be sorted in column A of
Master sheet tab of excel workbook. After sorting copy that data in a new
tab (Here it is there in Sheet 2).
Now apply the following formula in the Master sheet tab and drag it till the
end.

=MATCH($A:$A,Sheet 2!$A:$A,0)

So, where you have #N/A, means that particular item is not there in the
Sorted List.

Please click on "Yes" if it works. Thanks.
--
Dilip Kumar Pandey
MBA, BCA, B.Com(Hons.)
(e-mail address removed)
(e-mail address removed)
New Delhi, India

 

[Pecoflyer]

Hi there

We use Excel to create our orders and our order numbers start with TT-
(Eg:
TT-4056). We are at 7000 entries and I need a way to check that no
orders
have been left out.

How do I highlight any missing numbers in the sequence of order
numbers?
Eg:
TT-4055
TT-4056
TT-4058
TT-4059
I need to know that TT-4057 is missing.

Any help appreciated!!!

You can also use conditional formatting with following condition

=RIGHT(a2;4)*1-RIGHT(a1;4)>1

This will highlight the string just before the missing one


--
Pecoflyer

Cheers -
*'Membership is free' (http://www.thecodecage.com)* & allows file
upload ->faster and better answers

*Adding your XL version* to your post helps finding solution faster

 

[Jim Cone]

With your "numbers" starting in A1, enter this formula in B1 and fill down...
=IF(VALUE(RIGHT(A1,4)) = VALUE(RIGHT(A2,4))-1,"","Check")
The above assumes the actual numbers are the last 4 characters in each "number".
Trailing spaces must be removed from the "numbers" before using the formula.
--
Jim Cone
Portland, Oregon USA



"Liz" <[email protected]>
wrote in message
Hi there
We use Excel to create our orders and our order numbers start with TT- (Eg:
TT-4056). We are at 7000 entries and I need a way to check that no orders
have been left out.

How do I highlight any missing numbers in the sequence of order numbers?
Eg:
TT-4055
TT-4056
TT-4058
TT-4059
I need to know that TT-4057 is missing.
Any help appreciated!!!