sequential numbering exceptions
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! |
sequential numbering exceptions
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("D1:D50").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/200...e-elses-macro/ (General, Regular and Standard modules all describe the same thing.) Sub tryme() Range("D1:D50").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 -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Helen" wrote in message ... 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! |
sequential numbering exceptions
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! -- Daryl S "Helen" wrote: 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! |
All times are GMT +1. The time now is 03:32 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com