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:)

  • 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:)