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 again,

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

    Sub Level()
    Dim i As Long
    Application.ScreenUpdating = False
        For i = 2 To 2000
            Cells(i, 4).Value = Evaluate("=IF(C" & i & "=1,"""",INDEX(A:A,MATCH(LEFT(B" & i & ",(C" & i & "*8)-8),B:B,0)))")
        Next i
    Application.ScreenUpdating = True
    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!


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!