Posts by JoHa

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

    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

  • 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!