Missing Numbers in a Sequence

[Janet_Samuels]

I have a list of Invoice ID Numbers.  They should each be unique AND in sequence (so there should be no gaps in numbers).

I can easily check for duplicate Invoice ID Numbers.  However, I can't figure out how to determine if any Invoice ID Numbers are missing in the sequence.  In Excel, I would order the invoices by the Invoice ID Number.  Then I could check that the current row's Invoice ID number matches the previous number plus one.  Any that didn't match would be flagged.

I've tried using the "Multi-Row Formula" Tool where my Invoice ID Number is a "Double". I created a new field with the following expression:

[Row-1:InvoiceID]+1

I would have thought that this would take the previous row (the "Row-1") of the InvoiceID Field and add one to it. However, I get "1" as the data for each record in the new field

I've thought about using the summarize tool to get the Min and the count of the Invoice ID and then create a table from from the Minimum Invoice ID, and adding 1 for the COUNT number of times...but I can't figure out how to do that.

There's got to be an easy way to do this that I'm missing..........

[Felipe_Ribeir0 · Accepted solution]

Hi @Janet_Samuels 

I believe that the multi row formula is the way to go.

[Janet_Samuels]

Thank you!  That worked.  My issue was that I had clicked the "Group by" box...and thus I was getting inaccurate results.  Once I mimicked yours (WITHOUT checking that box, it worked like a charm!).

Janet

[RobertOdera · Accepted solution]

Hi, @Janet_Samuels 

Here you go, and this will also identify the missing Invoice ID.

Please mark this as an acceptable solution if this works for you.

Cheers!

[Janet_Samuels]

Thanks Robert...that solution works as well.  It is a bit longer of a workflow but it will identify the exact ID numbers that are missing (and if I have multiple missing ID numbers in the sequence (e.g. if 999750 and 999751 are both missing)

[RobertOdera]

You're most welcome, @Janet_Samuels.

Usually, after you flag an error, it is helpful to know (exactly) what you need (what is missing) 👌.

[ctvalderama]

Hi! Is it possible to configure this workflow where the column of invoice id is composed of alphanumeric characters? Thank you!

[aatalai]

@Janet_Samuels you could also possibly use the imputation tool