Excel Function

[ststern45]

Hi everyone,

Need help with a function/vba

I have the following 5 numbers from 1 to 30 in ascending order:

Notice below adding all the results would equal 5 (1-1-1-1-1 =5, 2-2-1-0-0 = 5, 5-0-0-0-0 = 5, 4-1-0-0-0 = 5, etc)

7 Possible Results
1-1-1-1-1
2-1-1-1-0
2-2-1-0-0
3-1-1-0-0
3-2-0-0-0
4-1-0-0-0
5-0-0-0-0

07-12-20-22-29
If the gap between each number not equal to 1 or no consecutive digits such as 01-02 etc. I would like the result to be 1-1-1-1-1

10-13-14-17-25
2-1-1-1-0

02-03-12-29-30
Above there are 2 consecutive numbers 02-03 and 29-30
Result = 2-2-1-0-0

01-02-03-07-14
Result = 3-1-1-0-0

08-09-25-26-27
Result = 3-2-0-0-0

3-04-05-06-08
4 consecutive numbers
Result = 4-1-0-0-0

01-02-03-04-05
5 consecutive numbers
Result = 5-0-0-0-0

Thank you!!

 

[Eric W]

How about:

Book1
	A	B	C
1	2-3-12-29-30		2-2-1-0-0
2	07-12-20-22-29		1-1-1-1-1
3	10-13-14-17-25		2-1-1-1-0
4	1-2-3-7-14		3-1-1-0-0
5	8-9-25-26-27		3-2-0-0-0
6	3-4-5-6-8		4-1-0-0-0
7	1-2-3-4-5		5-0-0-0-0
Sheet3

Cell Formulas
Range	Formula
C1	=getconsecutive(A1)

And this is the VBA code:

Function GetConsecutive(ByVal target As String)
Dim c(1 To 5) As Long, vals As Variant, Loc As Long, i As Long


    vals = Split(target, "-")
    c(1) = 1
    Loc = 1
    For i = 0 To 3
        If CInt(vals(i)) = CInt(vals(i + 1)) - 1 Then
            c(Loc) = c(Loc) + 1
        Else
            Loc = Loc + 1
            c(Loc) = 1
        End If
    Next i
    For i = 1 To 5
        GetConsecutive = GetConsecutive & WorksheetFunction.Large(c, i) & IIf(i = 5, "", "-")
    Next i
    
End Function

Let me know if you need help installing that. It may be possible with worksheet functions, but it would be quite unwieldy.

 

[Eric W]

Somewhat shorter version:

Function GetConsecutive(ByVal target As String)
Dim c(4) As Long, vals As Variant, Loc As Long, i As Long

    vals = Split(target, "-")
    c(0) = 1
    For i = 0 To 3
        If vals(i + 1) - vals(i) <> 1 Then Loc = Loc + 1
        c(Loc) = c(Loc) + 1
    Next i
    For i = 1 To 5
        GetConsecutive = GetConsecutive & WorksheetFunction.Large(c, i) & IIf(i = 5, "", "-")
    Next i
    
End Function

 

[ststern45]

Thank you soo much Eric
Works like a charm
Thanks
Steve

 

[Eric W]

Happy to help!

 

[ststern45]

I tried changing the code but it seems that since the digits begin with a zero it changes the calculation/outcome

Have a followup request using the same sets but extracting the 1st digit from each number:

7 Possible results

1-1-1-1-1
2-1-1-1-0
2-2-1-0-0
3-1-1-0-0
3-2-0-0-0
4-1-0-0-0
5-0-0-0-0





02-03-12-29-30
0-0-1-2-3 (1st digits from set above 02-03-12-29-30)
Result = 2-1-1-1-0


03-05-18-20-24
0-0-1-2-2
Result = 2-2-1-0-0


07-12-20-22-29
0-1-2-2-2
Result = 3-1-1-0-0


04-08-20-26-28
0-0-2-2-2
Result = 3-2-0-0-0


10-13-14-17-25
1-1-1-1-2
Result = 4-1-0-0-0



11-13-14-17-18
1-1-1-1-1
Result = 5-0-0-0-0


Note: 1-1-1-1-1 does not apply unless 1st digits range from 0 through 4
In the example above the 1st digits range from 0 through 3


Thank you!!

 

[ststern45]

Ok I figured out the new code.

Thanks again for all your help