sequential numbering exceptions

[Helen]

Easy exception reporting of sequential numbering.

I had a lengthy complex sequentially numbered worksheet by row. Lots of
things have now been added or taken away & I want a quick formulae to spot
errors in the numbering so I can amend them. The reason I can't just copy &
paste from row 1 to the end is that there are 3 levels - it goes something
like this(with many more rows at each level!!):
S01 level 1 allocation
S0101 level 2 sub allocation
S010101 level 3 sub allocation
S010102 as above
S010103 as above
S0102 level 2 sub allocation
S02 level 1 allocation

Does this make sense? Does anyone have a solution please? I have about 50
worksheets of long coding to check!

 

[Bernard Liengme]

Helen,
In the subroutine below I have assumed the serial numbers are in column A,
rows 1 to 50
and that the error messages go in column D. You should eb able to see how to
change the columns to suit your need.
The 1 in line reading >> mytest = Cells(1, "A") >> must be changed if the
first numbers is in another row
And change the 2 and 50 in >> For j = 2 To 50 >> to match the row of the
second and last numbers
and fix >> Range("D150").Clear >> and <<< Cells(j, "D") = "error >> as
needed

If you are new to macros
David McRitchie's site on "getting started" with VBA

http://www.mvps.org/dmcritchie/excel/getstarted.htm

Debra Dalgleish's "Adding Code to a Workbook"

http://www.contextures.com:80/xlvba01.html

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

Jon Peltier's site:
http://peltiertech.com/WordPress/2008/03/09/how-to-use-someone-elses-macro/

(General, Regular and Standard modules all describe the same thing.)


Sub tryme()
Range("D150").Clear
mytest = Cells(1, "A")
oldchar = Mid(mytest, 1)
num1 = Mid(mytest, 2, 2) * 10000
temp = Mid(mytest, 4, 2)
If temp = "" Then
num2 = 0
Else
num2 = temp * 100
End If
temp = Mid(mytest, 6, 2)
If temp = "" Then
num3 = 0
Else
num3 = temp * 1
End If
oldnum = num1 + num2 + num3

For j = 2 To 50
mytest = Cells(j, "A")
newchar = Mid(mytest, 1)
num1 = Mid(mytest, 2, 2) * 10000
temp = Mid(mytest, 4, 2)
Debug.Print "*"; temp; "*"
If temp = "" Then
num2 = 0
Else
num2 = temp * 100
End If
temp = Mid(mytest, 6, 2)
If temp = "" Then
num3 = 0
Else
num3 = temp * 1
End If
newnum = num1 + num2 + num3

If newchar < oldchar Or newnum < oldnum Then
Cells(j, "D") = "error"
End If
oldchar = newchar
oldnum = newnum2

Next j
End Sub


best wishes

 

[Daryl S]

You need to define 'out of sequence' a little better so we know what we are
looking for.

The approach I would take is to make a copy of the spreadsheet first, and
work with the copy. Then add a two new columns. In the first column, add
in row numbers, so you know the original sort order. Many easy ways to do
this, but if you use formulas, make sure to copy/paste special/values for the
column before you continue.

Then sort the spreadsheet by the sequence column. If any of the row numbers
are out of order, you know the sequences were out of order. To test for
this, in the second column you added, add the following formula (this
assumes the new columns you added were columns A and B, and this is the
formula in cell B1):
=IF(A1=A2-1,"","Sequence Change")
The last cell in the column will show "Sequence Change" just because there
isn't a cell below it.

Once that is done, you can look at all the records with the same size
sequence numbers, and do a similar check. You can add a column to pull the
LEN(C1) if column C is the one that now has the sequence number. Then sort
the entire range by this new column first and the sequence number second.

Change the formula in column B to the following. This new formula will
ignore the records where sequencing starts over at a 'inner' level, e.g.
S010103 to S010201
=IF(Right(C1,1) = "1","",IF(A1=A2-1,"","Sequence Change"))

Hope that helps!