Counting consecutive numbers in a comma-separated list

[mrc44]

I tried to find a formula or a macro for this online but I couldn't find anything.

I need a way to calculate the consecutive numbers in a comma separated list. Thanks in advance.

Here is a sample data: ConsecutiveCount.xlsm

I'm using Excel 2016.

* In the sample file, I put the expected results in separate cells, but it can be in a single too.

 

[jasonb75]

Please use the XL2BB add in to post your sample directly to the thread (link in my signature below), many people are unable to download linked files due to security restrictions.

 

[mrc44]

Thanks. this is what I did:

ConsecutiveCount.xlsm
	A	B	C	D	E	F	G	H
1	DATA	2 Consecutives	3 Consecutives	4 Consecutives	5 consecutives	6 consecutives	7 consecutives	8 consecutives
2	1,2,6,8,9,14,18	2
3	1,2,3,4			4
4	5,10,11,16,18,20	1
5	3,4,5,11,12,21,23	1	3
6	10,13,14,15,16,17				1
7
Sheet1

 

[jasonb75]

Having a list of numbers in one cell is not a good idea, it will be difficult to work with.

Is it possible that any given number could be repeated in any one cell?

Is there an absolute maximum number for the digits that appear in the cells? If we know that the numbers will always be less than 20 (as in the sample) then it can make things easier.

 

[mrc44]

there are no repetitions of numbers in the same cell. Yes, the number of cells is less than 20 in a cell.

 

[jasonb75]

Yes, the number of cells is less than 20 in a cell.

That is actually incorrect, A5 in your example goes up to 23 (I looked too quickly and missed it when I was typing my earlier reply).

 

[mrc44]

I'm sorry, my mistake, I meant to say there will be less than 20 comma-separated values. so there are 7 values in A5. but this is just a sample. so there won't be any number higher than 20. thanks.

 

[jasonb75]

With that in mind it should be possible with a reasonably simple formula. I'm not going to get chance now but will look at it tomorrow if it is not resolved by another member beforehand.

 

[lrobbo314]

As an example on row 5, it says 1 for 2 Consecutives, and 3 for 3 Consecutives.

Shouldn't those be 1 and 1? As in there is 1 pair of 2 consecutive numbers and 1 pair of 3 consecutive numbers?

 

[mrc44]

yes, sorry about that. it is 1 and 1. here is the corrected version:

ConsecutiveCount.xlsm
	A	B	C	D	E	F	G	H
1	DATA	2 Consecutives	3 Consecutives	4 Consecutives	5 consecutives	6 consecutives	7 consecutives	8 consecutives
2	1,2,6,8,9,14,18	2
3	1,2,3,4			4
4	5,10,11,16,18,20	1
5	3,4,5,11,12,21,23	1	1
6	10,13,14,15,16,17				1
Sheet1