Count consecutive/sequential IDs

[mystasz]

Hello,

I'm trying to identify and count where Invoice ID is sequential. (201, 202, 203). Non-sequential would be something like 210, 212, 220, per Vendor ID. 

If an Invoice ID has a string in it like "-W", it can be ignored but not dropped. Count would be 0. 

I'm looking to display two outputs:

1) Detailed view that would give all rows and columns (Expected 1 tab)

2) Summary view that would give one row per vendor ID (Expected 2 tab)

Attached is my sample data and two tabs for the two expected outputs. In the sample data set, the first invoice has a "-W" in it so it can be ignored with a count of 0. Rows 3-5 are sequential numbers (12302, 12303, 12304) for a total of 3. 

Thank you in advance!!!

[mystasz]

Understood, thank you. 

These invoice IDs are very messy and the goal is to evaluate those that are closest to a number where a sequence can actually be detected, even if it contains a string or decimal. An invoice ID can be MAY202111Z and in that case, I don't need to use that. Some cases explained below:

Case 1: Decimal

Case 2: String and -

Case 3: String and /

Case 4:  Slash (/)

Case 5: -

[gabrielvilella]

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. 

[mystasz]

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

[atcodedog05]

Your method is really amazing @gabrielvilella 🙂 got to learn few approaches 😀

[gabrielvilella]

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.

[gabrielvilella]

Thank you @atcodedog05! I am happy that you learned something from me. I just enjoy solving issues and teaching others 🙂