ststern45

Well-known Member
Joined
Sep 17, 2005
Messages
958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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!!
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
How about:


Book1
ABC
12-3-12-29-302-2-1-0-0
207-12-20-22-291-1-1-1-1
310-13-14-17-252-1-1-1-0
41-2-3-7-143-1-1-0-0
58-9-25-26-273-2-0-0-0
63-4-5-6-84-1-0-0-0
71-2-3-4-55-0-0-0-0
Sheet3
Cell Formulas
RangeFormula
C1=getconsecutive(A1)


And this is the VBA code:

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.
 
Upvote 0
Somewhat shorter version:

Code:
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
 
Upvote 0
Thank you soo much Eric
Works like a charm
Thanks
Steve
 
Upvote 0
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!!
 
Upvote 0
Ok I figured out the new code.

Thanks again for all your help
 
Upvote 0

Forum statistics

Threads
1,213,533
Messages
6,114,179
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top