ranking second number between two words

hi, can anyone help me please. i have a column of numbers. every so often in the column, there is the word 'start'. the amount of numbers between each 'start' varies, but i would like to put a 1 or a 0 in the column next to each number. i would like the second highest ranking number to get a 1 and all the others to get a 0.

if the first and second numbers between two starts are equal, then i would like all numbers between the 'start' to get a 0.

for example:

start
12 0
23 0
45 0
34 1
start
23 0
34 0
45 0
56 1
68 0
45 0
34 0
45 0
start
23 0
23 0
start


I posted on here months ago and someone very kindly did an array formula for me, trouble is some of the sheets are so long now that the array takes a few minutes to work it out and load up

As always, thanks if yyou can help

regards, jamie

Perhaps this will do what you wish:

Sub jp()
    Const sCol  As String = "A"    ' change to suit

    Dim rBeg    As Excel.Range
    Dim rEnd    As Excel.Range
    Dim sAdr1   As String
    Dim r       As Excel.Range
    Dim WF      As WorksheetFunction

    Set WF = WorksheetFunction

    Set rBeg = Columns(sCol).Find(What:="start", LookIn:=xlValues, LookAt:=xlWhole, _
                                  MatchCase:=False, _
                                  After:=Cells(Rows.Count, sCol), SearchDirection:=xlNext)
    If rBeg Is Nothing Then Exit Sub
    sAdr1 = rBeg.Address
    Do
        Set rEnd = Columns(sCol).FindNext(After:=rBeg)
        If rEnd.Address = sAdr1 Then Exit Do

        Set r = Range(rBeg, rEnd)
        If r.Rows.Count > 2 Then
            r.Offset(1, 1).Resize(r.Rows.Count - 2).Value = 0
            Set r = WF.Index(r, WF.Match(WF.Large(r, 2), r))
            r.Offset(, 1).Value = 1
        End If
        
        Set rBeg = rEnd
    Loop
End Sub

You're welcome, glad it worked for you.

Originally Posted by jamiepullen

Thanks alot! sheet loads in seconds (less than!) instead of minutes with the array formula.

Thanks, jamie

actually, on checking it this morning, i am not sure it does work quite right some numbers are ranked correct and some just award a '1' to the last number in the sequence

i get the following:



Start
99 0
96 0
106 0
95 0
97 0 This should be a '1'
93 0
78 0
0 0
0 1 This should be a '0'
Start
90 0


yet some numbers in the sequence are correct:

Start
80 0
68 0
77 1
0 0
0 0
0 0
Start

I am pretty sure i did it correct

VBa editor> double clicked the sheet i want it to work on under 'project-vba project' and pasted the code into the box and saved it. I would be grateful for any more help if anyone can.....


regards, jamie

sorry, on the above 99 should be a '1'

i have tried changing a few numbers etc in the code, but as i do not have a clue with vba, i am making a bit of a mess with it i think!!

Try this:

Sub jp1()
    Const sCol  As String = "A"    ' change to suit

    Dim rBeg    As Excel.Range
    Dim rEnd    As Excel.Range
    Dim sAdr1   As String
    Dim r       As Excel.Range
    Dim WF      As WorksheetFunction

    Set WF = WorksheetFunction

    Set rBeg = Columns(sCol).Find(What:="start", LookIn:=xlValues, LookAt:=xlWhole, _
                                  MatchCase:=False, _
                                  After:=Cells(Rows.Count, sCol), SearchDirection:=xlNext)
    If rBeg Is Nothing Then Exit Sub
    sAdr1 = rBeg.Address
    Do
        Set rEnd = Columns(sCol).FindNext(After:=rBeg)
        If rEnd.Address = sAdr1 Then Exit Do

        Set r = Range(rBeg, rEnd)
        If r.Rows.Count > 2 Then
            r.Offset(1, 1).Resize(r.Rows.Count - 2).Value = 0
            Set r = WF.Index(r, WF.Match(WF.Large(r, 2), r, 0))
            r.Offset(, 1).Value = 1
        End If
        
        Set rBeg = rEnd
    Loop
End Sub

sorted it. you are an excel genius!!!

Well, a genius would have gotten it right the first time ...

You're welcome, Jamie.

Hope you do not think i am being ungrateful here, as you have saved me loads of time and hassle already, but i was wondering if anyone could apply one tiny change to the above?......

I have played around and managed to assign a button to set the macro in action (which is a huge advancement for me!).

anyway, if i have used the macro, it displays the 1s and 0s as it should, but if the data changes and i need to run the macro again, the previous 1s and 0s stay there and are added to by the new 1s and 0s from rerunning the macro (if that makes sense!) i have to hilight the old 1s and 0s and delete them before clicking to run the macro again. Is it possible to add a bit to the macro in order for it to clear the old 1s and 0s from the previous run before it calculates the current data?

Like i said, you have saved me loads of hassle here, so if this is to much then i am still very grateful for the help you have given me so far

regards, jamie