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

1. 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

2. 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. 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.

4. 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. 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.

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