Missing Numbers in a Sequence

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..........

Help

Accepted answers

Felipe_Ribeir0

Hi @Janet_Samuels

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

multi.yxmd

RobertOdera

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!

MissingNumbersInSequence.yxzp

All comments

Felipe_Ribeir0

Hi @Janet_Samuels

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

multi.yxmd

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

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!

MissingNumbersInSequence.yxzp

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

Quick Links

This months top contributors

hhaigh1 280

FláviaB 262

JORGE4900 235

caltang 177

DanM 167