Sequential Numbers has always been a crucial control in Accounting, Financial and Business Operations. While sequential numbers are used, we (and auditors) know that we need to pay attention to those missing numbers in a sequence for irregularities.
Examples in real-life include:
It sounds straight-forward to examine broken sequences. However, in reality, this is a laborious task and sometimes not practical at all, as the number of data could be large, or there are too many sequences to examine. For instance, a listed company with 10 check accounts, each account used 15 check books in the previous 12 months. There are 150 sequences to examine!! So, this means, companies are all using a control which they cannot fully monitor.
Excel Power Expander provides a solid solution to advanced Excel users (especially professional practitioners) to tackle the everyday challenge of auditing sequential numbers, with the utility “Missing Sequentail Numbers Hunter”.
It is a indeed a very powerful utility. It can scan and report at very high speed. Most scanning would complete within 1 or 2 seconds, even for very large population of data.Also, it provides flexibility to tackle real-life situations. You can tell the software to consider only part of a string as a sequntial number. For example, in the data shown below, you can tell the software only from the 2nd to 6th character of the data in a scanning.
Below, you’ll see, step-by-step, a demonstration of how to use the “Missing Sequential Numbers Hunter” function to solve two real-life problems.
(Sample data in this example can be found in the worksheet “Example 1” of the file “sequence tester.xls”.)
We got a list of sequential number, 783 numbers. Some numbers within the sequence are missing. Also, the numbers are not sorted. Our objective is to find out what numbers in the sequence are missing.
First, select all the numbers (in Column A).
Run “Missing Sequential Numbers Hunter” from
the “Expander” menu.
You’ll see a screen like the one below. Notice that the software has automatically detected the minimum and maximum values in your selection. (In case you disagree with the software’s suggested lower and upper limits of your sequential numbers, you can change them here.)
Select “Adjacent to the source range” at the bottom section of the screen.
Click OK to proceed.
Within one second, “Missing Sequential Numbers Hunter” should have found out all the missing numbers and placed them in Column B.
Example 2 – Consider only part of a string
“Missing Sequential Numbers Hunter” is also useful in situation where only part of the sequential code should be considered.
(Sample data in this example can be found in the worksheet “Example 2” of the file “sequence tester.xls”.)
Consider data as shown in the following figure:
Notice that only from 2nd to 6th character of each code form a sequential number.
Again, within a second, you answer should have been placed in Column B.