Left side Values required with respect to the highlighted values in red colour

  • Hi all,
    Hope all is good.

    Can anyone please help me with any of the solution for my requirement as explained below ?

    Please see attached, the excel file for better understanding of my question.

    In the column 'B' (Sequence), there are certain values and just for my explanation, I have considered just 25 rows.

    I have highlighted few of the values in red colour in column 'B'.
    Reason being, I need only those values which are to the left of highlighted value and including the highlighted one as well as shown in the column 'C' (Output).
    I don't need any of the values which are to the right of highlighted value. It has to be deleted from Column 'C'.

    Is there any option (Formula or anything) where I can reduce my time ?
    Can anyone please suggest me on how I can do it in an efficient manner instead of entering manually for each and every row?

    [SIZE=14px]In short:[/SIZE]
    I need only the values which are to the left of highlighted value in red colour and including the red colour one. The one that are to the right of highlighted value must not be there in the output.

    Thank you all for your efforts and many Thanks in advance for your suggestions.

  • Here's a code.
    Loops through characters and finds the last red character. Change the "F" to "C" I just used "F" to test it out.

  • Hi Dave,
    Really appreciate your quick response with this fantastic solution. Thanks a lot for your solution.

    But just wanted to inform you that, just for understanding purpose I had highlighted certain values in Red colour. In actual, there is no unique colour. It will be in the same colour as like other values.

    If possible, can you "PLEASE" give me in a Formula manner instead of Code format ? It's a request and I am very desperate to see in a formula manner.

    Thanks a lot for your time and for the solution that you have provided!!!!!!

Participate now!

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