+ Reply to Thread
Results 1 to 10 of 10

Finding the second instance of a value after a text string

  1. #1
    Registered User
    Join Date
    01-07-2011
    Location
    Birmigham, UK
    MS-Off Ver
    Excel 2003
    Posts
    12

    Finding the second instance of a value after a text string

    Okay, so I have the following:

    COLUMN A COLUMN B

    Person A
    1 - BREAK
    11:30:00 00:05:00
    12:30:00 00:04:00
    1 - BREAK 00:09:00

    As you can see, for each Person I have a list of times that breaks were taken and the length of those breaks, followed by a total time. I essentially want to be able to pick up the total for each person. One thing to bear in mind though is that not each total is exactly four columns down (as depends on the amount breaks taken). As such, I need a formula that will allow me to search for the second instance of '1 - BREAK' after each persons name and then return the total time. I have about 20 different people's names, all different.

    I've tried to do this with vlookup or a combination of offset and match/index but realise the second instance of something can't be picked up with vlookup therefore a bit lost as to what I could try next.

    Is what I want to achieve actually possible?

    Thanks.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Finding the second instance of a value after a text string

    Assuming your data is in A1:B20 and your lookup person is in E1, then this formula:

    Please Login or Register  to view this content.
    would get the value in column B where the second "1 - BREAK" occurs in column A for the respective person.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    01-07-2011
    Location
    Birmigham, UK
    MS-Off Ver
    Excel 2003
    Posts
    12

    Re: Finding the second instance of a value after a text string

    Just perfect. Genius. Thanks so much for this. Reputation added...

  4. #4
    Registered User
    Join Date
    01-07-2011
    Location
    Birmigham, UK
    MS-Off Ver
    Excel 2003
    Posts
    12

    Re: Finding the second instance of a value after a text string

    Okay, this is working great, as stated above, but what if the "1 - BREAK" section is not directly after the person's name; say it could be the fourth category in the list? Can the formula also pick it up then?

  5. #5
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Finding the second instance of a value after a text string

    Assuming same parameters, try:

    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    01-07-2011
    Location
    Birmigham, UK
    MS-Off Ver
    Excel 2003
    Posts
    12

    Re: Finding the second instance of a value after a text string

    Excellent, this is exactly what I wanted. One final question, however - could it work backwards? So, for example if Person A was underneath the 1 - BREAK section; is there a way of picking it up that way around too?

  7. #7
    Registered User
    Join Date
    01-07-2011
    Location
    Birmigham, UK
    MS-Off Ver
    Excel 2003
    Posts
    12

    Re: Finding the second instance of a value after a text string

    If it worked backwards though, it would be the first instance that I wanted to pick up, not the second - sorry, should have mentioned that.

  8. #8
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Finding the second instance of a value after a text string

    Okay try:

    Please Login or Register  to view this content.
    This one needs to be confirmed with CTRL+SHIFT+ENTER not just ENTER to work. You will see { } brackets around it.

    Now, let's hope you don't ask for a diagonal direction or some helix patterned lookup

  9. #9
    Registered User
    Join Date
    01-07-2011
    Location
    Birmigham, UK
    MS-Off Ver
    Excel 2003
    Posts
    12

    Re: Finding the second instance of a value after a text string

    Okay, this is absolutely perfect, however... I would like it to pick up the 1st instance upwards of "1 - BREAK" rather than the second.

  10. #10
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Finding the second instance of a value after a text string

    I guess it is a matter of interpretation....

    By first, I assumed you meant the first "1 - Break" to appear above the person's name (but after the previous person's name).....

    Anyways, this will give the last "1 - Break" above the person's name... which hopefully is what you mean by 1st....

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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