Conditional Numbering of List Values

Re: Finding Next 1 In A List Of 0 And 1

Quote from A9192Shark

Hello All,

I have a list of 48 000 zeros and ones (0 and 1s) (and 12 separate lists).

If there is a 1 then I will return a zero.
If there is a zero then I need to find the next 1 and return the 'distance' to that 1.

For example
1
1
1
1
0
0
0
0
1

The first zero will have to return 4, the second 3 etc.

I have tried using Match(1,[range],0) but this takes for ever to run.
I could write a udf to do this but figure that it would have to be based on a loop. Can anyone think of a clever way of doing this?

In the mean time I will implement the udf.

Thanks,

Alan.

Display More

Ouch.

I would turn the data around so that you have 12 rows rather than 48000, read it directly from the data file (if you can) and try using the string operations such as Instr which can take a start point in the string to start searching from.

Just output the results into Excel.

If it's not a state secret, what are you doing ??

regards

Rich

Re: Finding Next 1 In A List Of 0 And 1

Hi,

Does

=IF(A1=0,MATCH(1,A2:$A$9,0),0)

this help you?

Re: Finding Next 1 In A List Of 0 And 1

Hi A9192Shark

Does this do what you want ?

Option Explicit


Function Build_Big_String() As String
         Dim New_String As String
         Dim R As Integer
         Dim Count As Long
         '*
         For Count = 1 To 48000
             R = Rnd(1)
             If R = 0 Then
                New_String = New_String & "0"
             Else
                New_String = New_String & "1"
             End If
         Next Count
         Build_Big_String = New_String
End Function


Sub Do_String()
    Dim The_String As String
    ReDim Zero_Counts(0) As Integer
    Dim R As Long
    '*
    The_String = Build_Big_String()
    Call Find_Zeros(The_String, Zero_Counts())
    For R = 0 To UBound(Zero_Counts) - 1
        Range("A" & (R + 1)).Value = Zero_Counts(R)
        If R < 650 Then
           Range("B" & (R + 1)).Value = "'" & Mid(The_String, 1 + (50 * R), 50)
        End If
    Next R
End Sub
'*
Sub Find_Zeros(Source As String, Zcount() As Integer)
    '*
    Dim State As String
    Dim Next_One As Long
    Dim Next_Zero As Long
    Dim Source_Len As Long
    Dim Here As Long
    '*
    '* Set the initial state
    '*
    State = Left(Source, 1)
    Here = 1
    Source_Len = Len(Source)
    While Here < Source_Len
          Select Case State
                 Case "0"
                      If Next_Zero <= Here Then
                         Next_Zero = InStr(Here, Source, "1")
                      End If
                      Zcount(UBound(Zcount)) = Next_Zero - Here
                      ReDim Preserve Zcount(UBound(Zcount) + 1)
                 Case "1"
                      '*
                      '* Ignore ones
                      '*
                      Here = InStr(Here, Source, "0") - 1
          End Select
          Here = Here + 1
          State = Mid(Source, Here, 1)
    Wend
End Sub

This takes about 2 seconds for one string, including the output.

Regards

Rich

Re: Finding Next 1 In A List Of 0 And 1

Quote from A9192Shark

Rich,

Thanks for the suggestion. I have solved the problem by telling my boss that it really is not required. This is a reasonable statement at this stage but we will want teh info later.

I will look into the idea of using a string to store the data. I think that I could implement something using this but it will be a peice of code rather than a function.

Your code runs very quickly because you fill the data from a loop, I have to fill it from a worksheet where the numbers are calculated.

Thanks for your help- I will look at using teh string when I solve this one later in teh year!

Cheers,

Alan.

Display More

Hi Alan,

What I'm trying to say is don't always get stuck on looking at data in the way it is presented to you at first.

It may be quicker to write a program to extract the 0's and 1's from the spreadsheet, put them into a string and then call the subroutines I've written rather than trying to do it directly from the spreadsheet.

Whatever you do, don't get it fixed in your head that you must use the data as is. If a bit of re-arranging (not creative mind you!) helps, and it's going to be faster in the long run, go for it!

Regards

Rich