+ Reply to Thread
Results 1 to 5 of 5

Find first and last instance of text in column range multiple times

  1. #1
    Registered User
    Join Date
    12-09-2011
    Location
    Eureka, CA
    MS-Off Ver
    Excel 2010
    Posts
    22

    Find first and last instance of text in column range multiple times

    Hi, I've made a spreadsheet which i use to check multiple staff time cards so that i can visually see that all 24 hours of each day have been covered by my staff. It works great for its purpose but I would like to extend its functionality a bit.

    On the sheet named "CopyMe" I have the time listed in half hour increments in cells B3-B50 and again in R3-R50. The top 2 Rows of this sheet have the date and Day. In cells C3-Q50 and S3-AH50 I type in the name of staff for each cell or half hour that they have worked. For example if "Jim" worked from 12pm-8pm on June 1st, I would fill in the name "Jim" in each cell in the Range C27-C42. The worksheet does some useful math for me in the bottom rows.

    I was hoping that on "Sheet1" I would be able to use formula to find the first instance of the name "Jim" in each column C-Q and S-AH and list the value from Column B in the corresponding Row(Time). Then I need another formula to find the last consecutive instance of the name "Jim". To make things more difficult, an employee could work more than one shift in a 24 hour day so the formula's would have to account for that somehow. For example the overnight worker starts at 10pm and ends at 8am the next morning. For example, lets say "Jon" works on June 1 from 10pm until June 2nd at 8am and the same shift again 24 hours later. That means if we only found the first instance of the name and the last instance of the name in each column, "Jon" would be credited with a 24 hour shift.

    I've filled out my example with the scenario's i've described above in an attempt to make this make sense. On "Sheet1" i've manually filled out my desired results. If anyone can help me accomplish this I would be forever grateful. Thank you excel guru's.

    Schedules.xlsx
    Last edited by ShannonHowell; 06-24-2012 at 05:51 AM.

  2. #2
    Registered User
    Join Date
    12-09-2011
    Location
    Eureka, CA
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: Find first and last instance of text in column range multiple times

    Example.xlsx sorry guys i think i attached the wrong file to the last post.....here is the one i meant to upload.

  3. #3
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Find first and last instance of text in column range multiple times

    Pl see the file attached with macro "Copyme".
    Clarifications welcome.
    If solved mark the thread solved.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    12-09-2011
    Location
    Eureka, CA
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: Find first and last instance of text in column range multiple times

    Thank you so much kvsrinivasamurthy, that worked fantastically. One thing that i noticed is that if you only cover one half hour the results come out with only a start time(no date or end time). That isn't a big problem as I am the only one who should ever be covering such a short amount of time but if there is an easy fix it would be cool. Also if you care to educate me as to how the macro works that would be cool as well. Thank you again kvsrinivasamurthy for providing the macro.....it is very much appreciated and it will be a big time saver for me.

  5. #5
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Find first and last instance of text in column range multiple times

    Thanks for the compliments.

    I have edited the macro .Now it takes care of single instant.(The problem you have expressed)

    Pl see the attached file.
    Thanks again.
    Attached Files Attached Files

+ 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