Excel: checking for sequential numbering

[Zorro]

I have an export from a database in Excel. Each record has a unique identifier. I would like to make sure the records are in a sorted order and each identifier is 1+identifier of the previous record.

Or, an example:

5 Apple
6 Orange
7 Banana
8 Monkey

I would like to do this in a single cell, ie without inserting a row. It should evaluate to TRUE (everything OK) or FALSE (record missing, duplicate etc.)

Anyone?

[Utanapishtim]

I don't have Excel in front of me, but...
For numbers in A1 to A100, how about:
{If(OR(max(A2:A100 - A1:A99) > 1, min(A2:A100 - A1:A99) < 1),FALSE,TRUE)}

[Brad Gile]

Originally Posted by Zorro

I have an export from a database in Excel. Each record has a unique identifier. I would like to make sure the records are in a sorted order and each identifier is 1+identifier of the previous record.

Or, an example:

5 Apple
6 Orange
7 Banana
8 Monkey

I would like to do this in a single cell, ie without inserting a row. It should evaluate to TRUE (everything OK) or FALSE (record missing, duplicate etc.)

Anyone?

Public Function IsOrdered(R As Range) As Boolean
'Unique record identifier is a (long) integer
'in Column 1

Dim J As Long
Dim Result As Boolean

Result = True 'set as Default

For J = 2 To R.Rows.Count
If R(J, 1) <> R(J - 1, 1) + 1 Then
Result = False
Exit For
End If
Next J

IsOrdered = Result

End Function

Am I missing something?

Brad

[Zorro]

I forgot to mention a strong preference for a non-VBA solution. (What they don't understand....)

Originally Posted by Utanapishtim

I don't have Excel in front of me, but...
For numbers in A1 to A100, how about:
{If(OR(max(A2:A100 - A1:A99) > 1, min(A2:A100 - A1:A99) < 1),FALSE,TRUE)}

Thanks, that put me on the right track! I now check if
- last number = first number + number of records - 1
- MAXA(OFFSET(A7,,,B3,1)-OFFSET(A6,,,B3,1) equals 1
Where the data starts at A6 and B3 contains the number of records

[Brad Gile]

Originally Posted by Zorro

I forgot to mention a strong preference for a non-VBA solution. (What they don't understand....)

Brad

[Hedges]

Zorro, how about the following?

Assuming your record identifiers are in cells C4:C8, then
{=SUM((((C5:C8)-(C4:C7))=1)*1)}
(Don't forget to enter as an array formula)

This counts the number of differences between sequential records that equal 1. If all your identifiers are sequential, then the result will be one less than the number of records, so you could modify it to the following
{=SUM((((C5:C8)-(C4:C7))=1)*1)-COUNT(C4:C8)+1}
In this case, anything other than a zero is problem.

Is this what you are looking for?

Hedges

[Utanapishtim]

Originally Posted by Zorro

Thanks, that put me on the right track! I now check if
- last number = first number + number of records - 1
- MAXA(OFFSET(A7,,,B3,1)-OFFSET(A6,,,B3,1) equals 1
Where the data starts at A6 and B3 contains the number of records

The trouble with just checking that is that if you have two numbers reversed somewhere in the middle, for example, it won't show up. Or any arrangement of the numbers in the middle, actually, as long as they're all there and the first and last are correct, right?

[snafu]

Assuming cells A2 through A7 contain the numbers 1 through 6

Then Cell A1 = {=AND((A2:A6 + 1) = A3:A7)}

Enter this as an array function by using Shift-Ctrl-Enter

Bon Appetit!

[Zorro]

Originally Posted by Utanapishtim

The trouble with just checking that is that if you have two numbers reversed somewhere in the middle, for example, it won't show up. Or any arrangement of the numbers in the middle, actually, as long as they're all there and the first and last are correct, right?

I might be wrong, but the second condition checks that the increase of the identifier is at most 1. Since, for n records, there needs to be a total increase of n to arrive at the last number (condition 1), each record will have to increase by exactly one.

[Zorro]

Originally Posted by snafu

Assuming cells A2 through A7 contain the numbers 1 through 6

Then Cell A1 = {=AND((A2:A6 + 1) = A3:A7)}

Enter this as an array function by using Shift-Ctrl-Enter

Bon Appetit!

Very creative!