6 Replies Latest reply: May 15, 2014 9:54 AM by Grant Perkins RSS

    Missing Check Sequence

    Brian Raley

      Searching for missing check sequences.

      Check Numbers in column A, 114,356 records.  There's to many records to copy and paste. I can create a column B that is identical to column A and that's about it.  What I would like to do is write a formula, cell A2 minus cell A1 and the result is entered into cell B2. 

      How do I proceed? 

      Can you help me find my missing check sequences?

      Thank you.

        • Missing Check Sequence
          Rusty_Haley _

          Brian,

           

          I would select/highlight the first half of the records then copy and paste them from Monarch into Sheet1 in Excel.  Select/highlight the second half of the records then copy and paste then into Sheet2.  This should give you 57178 records in each sheet, well under Excel's row max of 65000+.

           

          Assuming the check numbers are in column A and row 1 has header info, place the following formula in cell B2.

           

          =IF(A3<>A2+1,"Missing","")

           

          Drag this formula down through column B to check for missing numbers.

           

          This should identify non-consecutive check numbers.

           

          Hope this helps.

           

          Rusty

          • Missing Check Sequence
            Winn _

            Brian,

             

                 I think that I may have a solution to your problem using only Monarch. However, it requires that the report you are using be in check # order. Let me know if this is your case and I will post it. otherwise, it won't work.

            • Missing Check Sequence
              Grant Perkins

              Brian,

               

              Are you using Monarch 6?

               

              I'm looking forward to Winn's solution - I assume that even if the report is not in check number order the data could be re-sorted to give that.

               

              However one option would be to do the equivalent of an Excel "Vlookup". Again the records need to be in number order. One aditional requirement is to have a simple text file (or whatever but a text file would do it)which has a line for each of the POSSIBLE check numbers in the range.

               

              For example of the first known number expected was 100 and the last one 2006 you would need a list of all number starting 1000 end ending 2006. (Obviously numbers before the start or after the end could be included but would cause a little more analysis to be required).

               

              You may have a program or script to do that already based on a First Number/Last Number input prompt. You could use Excel using 2 or more Worksheets (Cell A1 = <start number> and then Cell A2 = (A11) and then copy that formula down the whole column. That would give the first 64k records. Cut the data and paste into a Text Editor. Repeat for the next number range etc. Save the text file .... and so on. A small script might be better!). You may even have another report that lists ALL POSSIBLE CHECKS in the range and so could use Monarch on that to create the master list.

               

              Take your report of numbers and create a number list. Add one calculated field to each record. It can contain anything you like since we are simply going to use it as a 'marker'. Save the 2 fields as a text file (or maybe even Access DB if you have Access). CSV would probably be appropriate but it's not too important.

               

              Now load you 'Master List' of possible numbers and create a template with a field for the check number. Use 'Join to Database' (might be different wording - I don't have my V6 system with me), select 'Local File' and connect to the file you created previously. The join should be based on the check number fields of course. That will leave the calculated 'flag' field to be displayed in the new output. Tick that field (and any other you may have created of course) and finish off the Join process.

               

              You should now have a list of ALL POSSIBLE numbers with a field that indicates if they were on your 'presented' list. What you want are the records with BLANK (or NULL) from the new list. Filter on the calculated field for blank (or NULL depending on your system preferences) and you should have a list of all the checks that are missing from the report.

               

              That's one way of dealing with it - I'll bet Winn has an simpler and more elegant offering tho!

               

              Sorry that this is not completely explicit and assumes that you have V6. As I mentioned I don't have a V6 system with me at the moment so have not been able to check if I have suggested anything that would not work. I did check this quickly a couple of days ago but for speed left my input file in Excel, which you can't use because of its row limits. I have used the 'flat file as database' option before and as far as I remember it offered no constraints that would affect this concept. I have also assumed that you have no text editor constraints in terms of the number of lines it could deal with. I've not tried this with a Windows based editor - it works with vi on Unix.

               

              Hope it helps or at least provides some ideas.

               

              Regards,

               

              Grant

               

                Originally posted by Brian Raley:

              Searching for missing check sequences.

              Check Numbers in column A, 114,356 records.  There's to many records to copy and paste. I can create a column B that is identical to column A and that's about it.  What I would like to do is write a formula, cell A2 minus cell A1 and the result is entered into cell B2. 

              How do I proceed? 

              Can you help me find my missing check sequences?

              Thank you.[/b][/quote]

               

              [size="1"][ November 22, 2002, 09:59 AM: Message edited by: Grant Perkins ][/size]

              • Missing Check Sequence
                Winn _

                Brian and others,

                 

                Sorry that I left you hanging for so long, but my original idea had several flaws that I could not overcome, so I have revised my approach to give the best results. So here it is:

                 

                First, run your report through Monarch to extract the check numbers and put them in ascending order. Export this table to a fixed length test file (.txt). Close your report and model.

                 

                Second, open the file that you just created into Monarch. (I usally try to avoid using a file that I exported from Monarch as input for Monarch, since usually there is a way to combine the operations into one model. Inthis case, that is not possible.)

                 

                Third, set up a detail template to capture all of the check numbers. Name the field with the check numbers LAST_CHECK.

                 

                It has all been fairly routine so far, but here is the twist that makes this work.

                 

                Fourth, set up an append template. Highlight  2 lines[/b] on the report. Create a new template.

                Make it an append type template. Trap on line 1, the same way you did in the previous step. However, highlight the check number on line 2. Name this field NEXT_CHECK.

                 

                So now you are wondering how is going to work. Well, here is a little secret most people don't think about. Unless you spcify otherwise on the Advanced tab of the Field Properties dialog box, there is nothing to stop Monarch from referncing the same line with multiple templates. So, what I have done is told Monarch to get all the check numbers, then append a field for all the check numbers that are on the next line.

                 

                Now since I know my check numbers are in order. The next step is easy.

                 

                Fifth, filter out consecutive check #s. If your fields are numeric type, then simply set up the following filter.

                [font="courier"]Filter Name: Missing_check

                Formula:NEXT_CHECK-LAST_CHECK>1  /font[/quote]If your check # are character type, you should be able to come up with a formula that does the same thing,

                 

                So, Monarch will scan your file and give you a, hopefully short, list of the last check # before and the next check # after any missing check #s.

                 

                See if that does the trick.

                • Missing Check Sequence
                  Brian Raley

                  Well, I broke down and used excel, sorry to let everyone down.  Currently, we are using Monarch V.3; maybe it's time for the company to upgrade.

                  However, is it possible to do what I needed to do in Monarch?  Let's make it simple I have column with ten check numbers.  We can create two identical columns.  What would be the formula I would use?

                   

                  Thanks for all the help.

                   

                  I'm going to try Winn's idea and I will let you know.

                   

                  Thanks again.

                  • Missing Check Sequence
                    Grant Perkins

                    Another nice solution from Winn. It certainly works in V5 and as I recall from v3 should be OK there as well.

                     

                    With a little playing it should alos be possible (for a numeric check number sequence) to calculate the missing number(s). If you know there is one number missing then the missing number must be the last number +1. If there is a gap of 4 then you could calculate 4 fields with each one adding one to the previous number. I guess if there are large blocks missing the idea has less value!

                     

                    I would certainly recommend V6, it offers a lot of useful additions and extends the opportunities significantly by comparison with 3, great tool that that was.

                     

                    Grant

                     

                      Originally posted by Brian Raley:

                    Well, I broke down and used excel, sorry to let everyone down.  Currently, we are using Monarch V.3; maybe it's time for the company to upgrade.

                    However, is it possible to do what I needed to do in Monarch?  Let's make it simple I have column with ten check numbers.  We can create two identical columns.  What would be the formula I would use?

                     

                    Thanks for all the help.

                     

                    I'm going to try Winn's idea and I will let you know.

                     

                    Thanks again.[/b][/quote]

                     

                    [size="1"][ November 23, 2002, 12:34 PM: Message edited by: Grant Perkins ][/size]