Count consecutive/sequential IDs

[MysticalAz]

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

[gabrielvilella]

Hey @MysticalAz, 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

[MysticalAz]

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 🙂

[gabrielvilella]

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

[MysticalAz]

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. 

[gabrielvilella]

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

[MysticalAz]

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.

[gabrielvilella · Accepted solution]

Hi @MysticalAz, 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. 

[MysticalAz]

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. 

[gabrielvilella]

So, what the REGEX_Match([Invoice ID], '\d+') does is checking if the invoice has only digits within it. If it has anything other than digits from 0 to 9, it will filter out. For thoses cases that you mentioned, you need to check if the number after the - or / or . are in sequence? So only invoiced that contains letters that should be filtered out?