+ Reply to Thread
Results 1 to 13 of 13

Only those values are required which are to the left of stated value

  1. #1
    Registered User
    Join Date
    06-26-2017
    Location
    India
    MS-Off Ver
    2010
    Posts
    81

    Only those values are required which are to the left of stated value

    Hi all,
    Hope all is good.

    Can anyone please help me with my problem which is 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. But in actual, there are 1000's of rows which are to be done.

    I have highlighted the value A2 in red.
    Reason being, I need only those values which are to the left of value A2 and including A2 as shown in the column 'C' (Output).
    I don't need any of the values which are to the right of value A2. 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 how can I do it in an efficient manner instead of entering manually for each and every row?

    Thank you all for your attempt and many Thanks in advance for your suggestions.
    Attached Files Attached Files

  2. #2
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Only those values are required which are to the left of stated value

    Enter in C2 and copy down
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  3. #3
    Registered User
    Join Date
    06-26-2017
    Location
    India
    MS-Off Ver
    2010
    Posts
    81

    Re: Only those values are required which are to the left of stated value

    Hi Alkey,
    I am extremely sorry for my delayed response.

    I am really thankful to the solution which you have provided. Thanks a lot!!!!! Its working very well.

    Thank you once again.

  4. #4
    Registered User
    Join Date
    06-26-2017
    Location
    India
    MS-Off Ver
    2010
    Posts
    81

    Re: Only those values are required which are to the left of stated value

    Hi Alkey,
    Sorry to bother you once again.

    Can you please tell me, whether can I use "OR" function in the formula which you have suggested ?
    The reason being, In the attached excel file I had told that I need only those values which are to the left of "A2".
    But now, there are some 5 to 6 values where I have to do.

    i.e. Assume A2 is in one cell and I need only those values which are to the left of "A2". And in the next cell, it will be "B2" and I need need only those values which are to the left of "B2" and again after few cells it will be "C2" and I need only those values which are to the left of "C2" and so on. Assume it repeats till "G2".

    I thought of applying filter and use the formula accordingly. But, is there anyway to add all these options in a single formula ?

    Thanks for the patience and thanks in advance for your suggestions.

  5. #5
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Only those values are required which are to the left of stated value

    I would suggest you upload an updated spreadsheet and show desired results.

  6. #6
    Registered User
    Join Date
    06-26-2017
    Location
    India
    MS-Off Ver
    2010
    Posts
    81

    Re: Only those values are required which are to the left of stated value

    Hi Alkey,

    Thank you for your quick response.
    Please find attached the revised Sample sheet for better understanding of my requirement.

    Thank you once again for helping me out on this question.
    Attached Files Attached Files

  7. #7
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Only those values are required which are to the left of stated value

    Try this
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Or
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by AlKey; 11-17-2017 at 01:38 PM.

  8. #8
    Registered User
    Join Date
    06-26-2017
    Location
    India
    MS-Off Ver
    2010
    Posts
    81

    Re: Only those values are required which are to the left of stated value

    Hi Sir Alkey,

    I don't have anything to comment on your formula which you have suggested. Yet I just want to say its "AWESOME"!!!!!!!!! Working as expected.
    Thanks a lot for your formulas.

    At last, if you don't mind and when you have time, can you please explain me how those 2 formulas work. I did not understand the formula.

    Thank you once again for your solution.

  9. #9
    Registered User
    Join Date
    06-26-2017
    Location
    India
    MS-Off Ver
    2010
    Posts
    81

    Re: Only those values are required which are to the left of stated value

    Hi Alkey,
    Sorry to bother you once again.
    Till yesterday, I was using your formula as you have stated above and It's working perfectly.

    Can you help me once again as per the attached excel file (Rev 2).
    Previously there were 2 characters everywhere (like A2, B2, C2 D2 etc)
    But now there are random number of characters as reference (like A2, A2C, B2, BA2, C2AD, EF2, G etc) and I have highlighted those in red colour.
    Please see attached excel file (Rev 2).

    Many thanks in advance.
    And thanks for your time and patience.
    Attached Files Attached Files

  10. #10
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Only those values are required which are to the left of stated value

    Since your lookup values are growing in numbers and now of the different length, the formula has changed a bit.
    There are two options that I can offer:
    1st formula is with built-in values and the 2nd formula that uses lookup list.
    I have added a column (Column E) as a lookup table to make formula shorter and easier to manage. I would advise to use lookup table.

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Or
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Formulas entered in C2 and copied down
    raju.r.083.JPG
    Attached Files Attached Files
    Last edited by AlKey; 12-02-2017 at 07:47 PM.

  11. #11
    Registered User
    Join Date
    06-26-2017
    Location
    India
    MS-Off Ver
    2010
    Posts
    81

    Re: Only those values are required which are to the left of stated value

    Hi Sir Alkey,
    Thank you once again for providing multiple solutions.
    This time also its working fantastic.
    Thanks a lot!!!!!!!

  12. #12
    Registered User
    Join Date
    06-26-2017
    Location
    India
    MS-Off Ver
    2010
    Posts
    81

    Re: Only those values are required which are to the left of stated value

    Hi Sir Alkey,
    I am extremely sorry to disturb you a lot.

    Now again some minor modifications has been done and I am unable to edit your formula as per my requirement.
    Can you please help me out again on this?

    Requirement is same as the previous one (as shown in the attached excel file Rev2.1).

    Modifications that are done:
    1) Separator used is Semi colon ( instead of Comma (,).
    2) Hyphen (-) is used to pair with every character (Eg: A2-H7; G2-N9 etc) and is shown in attached excel file Rev2.1.

    Many Thanks in advance for your time and for your patience in helping me out with this problem.
    Thanks a lot!!!!!!
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    06-26-2017
    Location
    India
    MS-Off Ver
    2010
    Posts
    81

    Re: Only those values are required which are to the left of stated value

    Hi Sir Alkey,
    Need your help desperately on this problem.

    Can you kindly have a look on this and provide me your solution ?

    Many Thanks in advance..

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Application.Width = 75% of Stated Screen Resolution. What is causing this?
    By SymphonyTomorrow in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-30-2014, 01:38 PM
  2. Replies: 4
    Last Post: 11-02-2011, 07:58 AM
  3. Creating groups based on stated preference?
    By Nosnibor in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-10-2010, 08:10 AM
  4. Replies: 0
    Last Post: 02-13-2009, 03:56 PM
  5. How can I make an error message display when a required cell is left blank?
    By saulmgf in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-04-2008, 03:45 PM
  6. In Excel, how to make a field required so it's not left blank?
    By linplex in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-05-2006, 10:15 AM
  7. Matching cells (RE-STATED)
    By GARY in forum Excel General
    Replies: 0
    Last Post: 02-14-2006, 03:00 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1