Count consecutive/sequential IDs

Hi @mystasz, I was assuming the invoice number would always go up, but on this case you have a higher number on top. After a great night of sleep I was able to even make the Sequence ID formula simpler. See attached.

All comments

Hey @mystasz, I looked at your sample data and I have a question. For each sequence of consecutives IDs, you need the count of how many consecutives there are, that is why you have the number 3 on the Unique Count of Consecutive Invoice IDs column?

The second sequence of consecutives starts at the ID 103, isn't it? You have it starting on the 102 on your file, but the one before this is the 100. Also, following the logic of what I mentioned first, I have set the second sequence to 2, as there is a sequence of two consecutives.

Vendor ID	Vendor Name	Invoice ID	Unique Count of Consecutive Invoice IDs
10000	Pepper	12301-W	0
10000	Pepper	12302	3
10000	Pepper	12303	3
10000	Pepper	12304	3
10000	Pepper	12310	0
10000	Pepper	12320	0
10000	Pepper	100	0
10000	Pepper	102	0
10000	Pepper	103	2
10000	Pepper	104	2
20000	Salt	200-Y	0

Hi @gabrielvilella!

Yes to your first question. For your second question, it would start at 102 not 103 because 102, 103 and 104 are consecutive numbers. 100 would not be part of that count. Thank you 🙂

Hi @mystasz, thank you for clarifying that. See a solution below:

This is very impressive!!

I'm attaching another sample set because the first output was incorrect and I believe its because its set to find the max. For example, 1 vendor can have multiple sequential invoice numbers.

Vendor A can have invoice IDs 1, 2, 3 and Vendor A can also have invoice IDs 10, 11, 12, 13, 14. Which would be two groups of sequential numbers. First group would be count of 3 for each row and second group would be count of 5 for each row.

See attached sample and expected.

Sequential Numbers Sample Data 2.xlsx

I had to modify it a litte to acomodate the option that two two groups of sequential numbers can happen one after the other.

Hey @gabrielvilella. This seems to be working on the sample data but for some reason not on my actual data.

Can you elaborate the Sequence ID formula you used? For the screenshot below, i'm assuming 00421 and 00422 should have a Sequence ID of 3 but continues on with 2. The rows below should be 4. It seems to be capturing Sequence ID 2 for the ones that were consecutive and not consecutive.

Sequential Numbers Sample Data 3.xlsx

Yes!! That seems to do it.

My last question to you would be regarding the Regex_Match. Can you elaborate what this does exactly? "REGEX_Match([Invoice ID], '\d+')"

I have Invoice IDs like the below that are being filtered out because of a "-" or "/" or decimal within it.

Hey @mystasz, what if you just remove anything that is not a digit from those invoices and then check if they are sequencial. For example:

Invoice ID	Invoice Number	Count Consecutive
2021.01	202101	3
2021.02	202102	3
2021.03	202103	3
2022.04	202204	2
2022.04	202204	2
W102/01	10201	2
W102/02	10202	2

On this case the MAY202111Z would be transformed to 202111 and then analysed. If you do not wish that to happen, there needs to be a rule to remove those. Same goes to the inicial example 12301-W. One way of removing those is checking if they are ending with a letter, for instance.

Yup something like that would need to happen. Appreciate your suggestion and your help with this.

So, I just applied those conditions I mentioned (removing if it ends on a letter and transforming into a number). Here is one way of doing this.