merge & sort non-adjacent cells

**BeatBama** · 01-12-2011, 10:55 AM

I have a timekeeping report that runs in this format:
(A)Agent Name -- (B)Date -- (C)Login Time -- (D) Logout Date -- (E)Logout Time
employee A. 1/3/2011 7:53AM 1/3/2011 10:05 AM
employee A 1/3/2011 10:20 AM 1/3/2011 12:30 PM
employee A 1/3/2011 1:16 PM 1/3/2011 2:45 PM
employee A 1/3/2011 3:00 PM 1/3/2011 4:45 PM
employee B 1/3/2011 7:59 AM 1/3/2011 10:29AM
employee B 1/3/2011 10:44 AM 1/3/2011 12:00 PM
employee B 1/3/2011 12:45 PM 1/3/2011 3:12 PM
employee B 1/3/2011 3:27 PM 1/3/2011 4:45 PM

and want it be able to drop it an spreadsheet in this format and have it automatically fill C with the data:
(A)Employee -- (B) Event -- (C)Log Time
Employee A Start of Day 7:53:00 AM
Employee A Break 1 Starts 10:05:00 AM
Employee A Break 1 Ends 10:20:00 AM
Employee A Lunch Starts 12:30:00 PM
Employee A Lunch Ends 1:16:00 PM
Employee A Break 2 Starts 2:45:00 PM
Employee A Break 2 Ends 3:00:00 PM
Employee A End of Day 4:45:00 PM
Employee Event Log TimeEmployee B Start of Day 7:59:00 AM
Employee B Break 1 Starts 10:29:00 AM
Employee B Break 1 Ends 10:44:00 AM
Employee B Lunch Starts 12:00:00 PM
Employee B Lunch Ends 12:46:00 PM
Employee B Break 2 Starts 3:12:00 PM
Employee B Break 2 Ends 3:27:00 PM
Employee B End of Day 4:45:00 PM

Which is basically merging C & E, and sorting in order - C3 E3 C4 E4 C5 E5
I can do a formula to have it recognize the name, but can't think of a good way to get the times into one column. Excel 2003. Any tips greatly appreciated!

**ChemistB** · 01-12-2011, 11:15 AM

Not sure what you are asking for. =B2+C2 should give you date time within a single cell, format the column as date and time. Then D2 + E2 for logout date - time.

**BeatBama** · 01-12-2011, 11:48 AM

the date isn't really important. It's on the report, but report can be run for a single day to make it moot. The issue is getting the times from C & E to list in a single column.

**JBeaucaire** · 01-12-2011, 11:55 AM

This is going to be easiest to help you if you create a sample workbook showing exactly how the data looks and is laid out, and a mockup of exactly how you want your report to come out.

Your initial example above only has 2 examples and both examples have 4 punches for each day. Surely there are variations to that!? Make sure your sample workbook demonstrates all the variations that will occur in the data then your sample Results shows how those are to be processed.

In others words, you need to make all the decisions regarding how each situation is handled before we dive into creating long formulas and/or macros.

Lastly, I would expect a macro would be best for handling this, are you OK with using VBA?

**BeatBama** · 01-12-2011, 01:03 PM

OK, I put both on different sheets in one workbook and color-coded to help give a visual.

I've not written a macro before, but can add that toolbar and probably figure out how to put it in.

**BeatBama** · 01-12-2011, 01:15 PM

Oh, and there can be variations...someone may not take breaks one day because they're leaving 30 minutes early, or may only work a half day. The sheet basically just measures the time between when they clock out to when they clock in again, and if breaks are >15 mins or lunch >45 mins it gives a gives a tardy. Those exceptions can be manually keyed, though.
Another twist is that the main report may have 90 people on it, and those 90 are on 6 different teams of 15 each - that's why I'll need to have it search the 90 for a name match.

**JBeaucaire** · 01-12-2011, 01:16 PM

You didn't respond to my query about the number of entries per day for each person. Your new file is no different from the example in post #1, each person has 4 rows of punches per day.

Surely that isn't always the case? There is never any variation in that at all? Ever?

If so, your sample date must include examples of fewer punches, more punches, whatever happens in your real use. Then the sample results must show how you resolve each of those scenarios and explain it if it's not obvious.

Don't add verbal examples of what's different, put that into the sample data, all of it, and then resolve what should happen on the summary for each exception.

**leo73pk** · 01-12-2011, 01:41 PM

@beatbama,

the spreadsheet you provided in somewhat confusing, these are output reports we appreciate if you could provide us your actual sample data.

**BeatBama** · 01-12-2011, 02:11 PM

Typically there are 4 rows, with two Times on each...a login and logout.
Getting those 4 rows of two each to a single row of 8 was the original object.
I think both examples are listed as such.

I'll attach copies of the actual worksheets.
......A........B........C.........D....
Date - Login - Date - Logout is the starting format.
2
3
4
5

What this person is currently doing is manually cutting and pasting
B2
D2
B3
D3
B4
D4
B5
D5

to get the times into one column in chronological order. my hope was there would be a way to have that step automated. If someone has fewer than 8 times for a given day, they just paste as needed rather than all 8 at once.

if it can do more, that's great, but just getting those 4 rows of 2 to a single row of 8 would be a big help.