Macro to compare current cell value to the previous cell value then loop

  • I need help create a macro to do logic below:


    Suppost B10 is the active cell, I want to


    Compare value of B10 to B9, if B9 is one less than B10 them copy value from A9 to C10
    If not - then - compare value of B10 to value of B8 and keep going backward until I find a cell with value of one less than B10 (pertend it is in B3)
    then copy value in cell A3 to C10



    Thanks
    Joy

  • Re: Macro to compare current cell value to the previous cell value then loop


    Thank you. It works great but I think I did not ask the right question. I want macro to start at row B1 -- there nothing to compare then copy A1 to C1
    then compare B2 to B1 - if B1 is not one less than B2 then copy A2 to C2 then
    B3 to B2 if B3 is not one less than B2 then compare B3 to B1 (said B1 is one less than B3) copy A1 to C3 then keep going with compare B4 to B3 and so on... until last record. This should happen when I ran macro.


    Thank you

  • Re: Macro to compare current cell value to the previous cell value then loop


    Your logic is still missing pieces... in your rewrite there is no way of telling the replacement to stop. You need to supply better rules on what you want or send a spreadsheet demonstrating the results you want.


    I check in tomarrow ---

  • Re: Macro to compare current cell value to the previous cell value then loop


    Thank you for your help. Table below is what I am trying to do. I have over 2000 line items, it is not possible to do this manually. Is there a macro that can make it happen?


    [TABLE="width: 166"]

    [tr]


    [TD="width: 21, bgcolor: transparent"][/TD]
    [TD="class: xl67, width: 82, bgcolor: transparent"]Data[/TD]
    [TD="width: 64, bgcolor: transparent"]Data[/TD]
    [TD="class: xl67, width: 53, bgcolor: transparent"]Result[/TD]

    [/tr]


    [tr]


    [TD="bgcolor: transparent"][/TD]
    [TD="class: xl68, bgcolor: #fcd5b4"]Col.A[/TD]
    [TD="class: xl65, bgcolor: #fcd5b4"]Col.B[/TD]
    [TD="class: xl68, bgcolor: #fcd5b4"]Col.C[/TD]

    [/tr]


    [tr]


    [TD="bgcolor: transparent, align: right"]1[/TD]
    [TD="class: xl69, bgcolor: transparent"]A[/TD]
    [TD="class: xl66, bgcolor: transparent"]1[/TD]
    [TD="class: xl71, bgcolor: #b8cce4"] [/TD]

    [/tr]


    [tr]


    [TD="bgcolor: transparent, align: right"]2[/TD]
    [TD="class: xl69, bgcolor: transparent"]B[/TD]
    [TD="class: xl66, bgcolor: transparent"]2[/TD]
    [TD="class: xl72, bgcolor: #b8cce4"]A[/TD]

    [/tr]


    [tr]


    [TD="bgcolor: transparent, align: right"]3[/TD]
    [TD="class: xl69, bgcolor: transparent"]C[/TD]
    [TD="class: xl66, bgcolor: transparent"]3[/TD]
    [TD="class: xl72, bgcolor: #b8cce4"]B[/TD]

    [/tr]


    [tr]


    [TD="bgcolor: transparent, align: right"]4[/TD]
    [TD="class: xl69, bgcolor: transparent"]D[/TD]
    [TD="class: xl66, bgcolor: transparent"]4[/TD]
    [TD="class: xl72, bgcolor: #b8cce4"]C[/TD]

    [/tr]


    [tr]


    [TD="bgcolor: transparent, align: right"]5[/TD]
    [TD="class: xl69, bgcolor: transparent"]E[/TD]
    [TD="class: xl66, bgcolor: transparent"]5[/TD]
    [TD="class: xl72, bgcolor: #b8cce4"]D[/TD]

    [/tr]


    [tr]


    [TD="bgcolor: transparent, align: right"]6[/TD]
    [TD="class: xl69, bgcolor: transparent"]F[/TD]
    [TD="class: xl66, bgcolor: transparent"]6[/TD]
    [TD="class: xl72, bgcolor: #b8cce4"]E[/TD]

    [/tr]


    [tr]


    [TD="bgcolor: transparent, align: right"]7[/TD]
    [TD="class: xl69, bgcolor: transparent"]G[/TD]
    [TD="class: xl66, bgcolor: transparent"]7[/TD]
    [TD="class: xl72, bgcolor: #b8cce4"]F[/TD]

    [/tr]


    [tr]


    [TD="bgcolor: transparent, align: right"]8[/TD]
    [TD="class: xl69, bgcolor: transparent"]H[/TD]
    [TD="class: xl66, bgcolor: transparent"]8[/TD]
    [TD="class: xl72, bgcolor: #b8cce4"]G[/TD]

    [/tr]


    [tr]


    [TD="bgcolor: transparent, align: right"]9[/TD]
    [TD="class: xl69, bgcolor: transparent"]I[/TD]
    [TD="class: xl66, bgcolor: transparent"]9[/TD]
    [TD="class: xl72, bgcolor: #b8cce4"]H[/TD]

    [/tr]


    [tr]


    [TD="bgcolor: transparent, align: right"]10[/TD]
    [TD="class: xl69, bgcolor: transparent"]J[/TD]
    [TD="class: xl66, bgcolor: transparent"]9[/TD]
    [TD="class: xl72, bgcolor: #b8cce4"]H[/TD]

    [/tr]


    [tr]


    [TD="bgcolor: transparent, align: right"]11[/TD]
    [TD="class: xl69, bgcolor: transparent"]K[/TD]
    [TD="class: xl66, bgcolor: transparent"]10[/TD]
    [TD="class: xl72, bgcolor: #b8cce4"]J[/TD]

    [/tr]


    [tr]


    [TD="bgcolor: transparent, align: right"]12[/TD]
    [TD="class: xl69, bgcolor: transparent"]L[/TD]
    [TD="class: xl66, bgcolor: transparent"]10[/TD]
    [TD="class: xl72, bgcolor: #b8cce4"]J[/TD]

    [/tr]


    [tr]


    [TD="bgcolor: transparent, align: right"]13[/TD]
    [TD="class: xl69, bgcolor: transparent"]M[/TD]
    [TD="class: xl66, bgcolor: transparent"]9[/TD]
    [TD="class: xl71, bgcolor: #b8cce4"]H[/TD]

    [/tr]


    [tr]


    [TD="bgcolor: transparent, align: right"]14[/TD]
    [TD="class: xl69, bgcolor: transparent"]N[/TD]
    [TD="class: xl66, bgcolor: transparent"]9[/TD]
    [TD="class: xl71, bgcolor: #b8cce4"]H[/TD]

    [/tr]


    [tr]


    [TD="bgcolor: transparent, align: right"]15[/TD]
    [TD="class: xl69, bgcolor: transparent"]O[/TD]
    [TD="class: xl66, bgcolor: transparent"]9[/TD]
    [TD="class: xl71, bgcolor: #b8cce4"]H[/TD]

    [/tr]


    [tr]


    [TD="bgcolor: transparent, align: right"]16[/TD]
    [TD="class: xl69, bgcolor: transparent"]P[/TD]
    [TD="class: xl66, bgcolor: transparent"]8[/TD]
    [TD="class: xl71, bgcolor: #b8cce4"]G[/TD]

    [/tr]


    [tr]


    [TD="bgcolor: transparent, align: right"]17[/TD]
    [TD="class: xl70, bgcolor: transparent"]Q[/TD]
    [TD="class: xl66, bgcolor: transparent"]8[/TD]
    [TD="class: xl71, bgcolor: #b8cce4"]G[/TD]

    [/tr]


    [tr]


    [TD="bgcolor: transparent, align: right"]18[/TD]
    [TD="class: xl70, bgcolor: transparent"]R[/TD]
    [TD="class: xl66, bgcolor: transparent"]9[/TD]
    [TD="class: xl71, bgcolor: #b8cce4"]Q[/TD]

    [/tr]


    [tr]


    [TD="bgcolor: transparent, align: right"]19[/TD]
    [TD="class: xl70, bgcolor: transparent"]S[/TD]
    [TD="class: xl66, bgcolor: transparent"]9[/TD]
    [TD="class: xl71, bgcolor: #b8cce4"]Q[/TD]

    [/tr]


    [tr]


    [TD="bgcolor: transparent, align: right"]20[/TD]
    [TD="class: xl70, bgcolor: transparent"]T[/TD]
    [TD="class: xl66, bgcolor: transparent"]9[/TD]
    [TD="class: xl71, bgcolor: #b8cce4"]Q[/TD]

    [/tr]


    [tr]


    [TD="bgcolor: transparent, align: right"]21[/TD]
    [TD="class: xl70, bgcolor: transparent"]U[/TD]
    [TD="class: xl66, bgcolor: transparent"]8[/TD]
    [TD="class: xl71, bgcolor: #b8cce4"]G[/TD]

    [/tr]


    [tr]


    [TD="bgcolor: transparent, align: right"]22[/TD]
    [TD="class: xl70, bgcolor: transparent"]V[/TD]
    [TD="class: xl66, bgcolor: transparent"]9[/TD]
    [TD="class: xl71, bgcolor: #b8cce4"]U[/TD]

    [/tr]


    [tr]


    [TD="bgcolor: transparent, align: right"]23[/TD]
    [TD="class: xl70, bgcolor: transparent"]W[/TD]
    [TD="class: xl66, bgcolor: transparent"]9[/TD]
    [TD="class: xl71, bgcolor: #b8cce4"]U[/TD]

    [/tr]


    [tr]


    [TD="bgcolor: transparent, align: right"]24[/TD]
    [TD="class: xl70, bgcolor: transparent"]X[/TD]
    [TD="class: xl66, bgcolor: transparent"]10[/TD]
    [TD="class: xl71, bgcolor: #b8cce4"]W[/TD]

    [/tr]


    [/TABLE]

  • Re: Macro to compare current cell value to the previous cell value then loop


    Please share your solution so that this *DOES NOT* become a dead-end thread for others who land here after searching the forum for a solution to a similar need.


    Thanks.

  • Re: Macro to compare current cell value to the previous cell value then loop


    Index+Sumproduct formula works for me. Formula is in Column C and D1 respresent item you wanted to lookup.


    =INDEX($A:$A,SUMPRODUCT(MAX(($B:$B=D1)*ROW($B:$B))-ROW($A:$A)+1)