Do Until command, return value from different column on resulting row

  • I want to return the value in column A based on the value in column C being (value in current row column C)-1.

    There could be up to 2000 rows, so a basic 'nested if' won't be anywhere near enough.
    I know it probably involves Do Until, but I'm incredibly rusty on macros - but on a time-pressure from work to complete the task. Any assistance much appreciated!

    The attached file is a simplified example, tweaked from a manufacturing Bill of Materials (BOM).
    Column B has a block of 8 digits to visually represent the line number and sub-levels, as per the manufacturing software. I've converted it to a level number in column C.


    Formula/macro is required for each cell in column D.

    For example, to derive cell D21:

    Cell C21 is at level 4.
    Look to each row above until you reach level 3. In this instance, the first level 3 it meets above C21 is in C16.
    Result is contents of A16, ie P082A79098WE.
    Disregard any other level 3's higher up the BOM.
    Disregard completely if level = 1.

    Thank you!

  • Hello and Welcome to the Forum :)


    Thanks to the underlying logic of your numbering system ... a simple formula can handle your request ...;)


    see attached test file


    Hope this will help

    :)

    Files

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hello again,


    If you need the macro equivalent for over 2'000 rows ... below is a proposal :

    Code
    1. Sub Level()
    2. Dim i As Long
    3. Application.ScreenUpdating = False
    4. For i = 2 To 2000
    5. Cells(i, 4).Value = Evaluate("=IF(C" & i & "=1,"""",INDEX(A:A,MATCH(LEFT(B" & i & ",(C" & i & "*8)-8),B:B,0)))")
    6. Next i
    7. Application.ScreenUpdating = True
    8. End Sub

    Hope this will help

    :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Once you have tested both the Formula and the Macro ... feel free to share your comments ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Thank you so much, Carim. You're an absolute star! :saint:

    I can get this task completed now, and then learn more about Index and Match functions.


    Coincidentally, I have a note to look at these functions, along with the new xlookup and xmatch, as prompted by Excel help. I've just been so busy with this project that I haven't been able to put any time aside for it yet. I currently use vlookups with embedded hlookups because the data set headers can vary. It's probably not the most efficient way.

    Time to put aside a few hours for training!


    Joanne

  • Glad you could fix your problem :)


    Thanks a lot... Joanne ... for your very kind words ... AND for the Like :thumbup:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)