VBA to concatenate all cells of the same date and agent with breaks

[GMLee]

Excel 2007
Windows XP


Hello! I currently have a set of data for the activities of agents for up to 6 weeks of dates. Right now each activity is on its own row. I would like to have only one row for each agent for each date and combine all the activities in on cell with a break after each activity.

This is how the data currently appears:
[TABLE="class: grid, width: 500"]
<tbody>[TR="bgcolor: #E0E0F0"]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[TH]C[/TH]
[TH]D[/TH]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]Job Title[/TD]
[TD]Name[/TD]
[TD]Date[/TD]
[TD]Activity[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]JobTitle1[/TD]
[TD]Agent1[/TD]
[TD]8/5/2012[/TD]
[TD]Phone 09:30-10:37[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]JobTitle1[/TD]
[TD]Agent1[/TD]
[TD]8/5/2012[/TD]
[TD]FDR 10:37-10:57[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]JobTitle1[/TD]
[TD]Agent1[/TD]
[TD]8/5/2012[/TD]
[TD]Phone 10:57-12:00[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]JobTitle1[/TD]
[TD]Agent1[/TD]
[TD]8/5/2012[/TD]
[TD]Break 12:00-12:15[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD]JobTitle1[/TD]
[TD]Agent1[/TD]
[TD]8/5/2012[/TD]
[TD]Phone 12:15-13:06[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD]JobTitle1[/TD]
[TD]Agent1[/TD]
[TD]8/5/2012[/TD]
[TD]FDR 13:06-13:31[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD]JobTitle1[/TD]
[TD]Agent1[/TD]
[TD]8/5/2012[/TD]
[TD]Phone 13:31-14:00[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD]JobTitle1[/TD]
[TD]Agent1[/TD]
[TD]8/5/2012[/TD]
[TD]Lunch 14:00-14:30[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD]JobTitle1[/TD]
[TD]Agent1[/TD]
[TD]8/5/2012[/TD]
[TD]FDR 14:30-16:15[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD]JobTitle1[/TD]
[TD]Agent1[/TD]
[TD]8/5/2012[/TD]
[TD]Break 16:15-16:30[/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD]JobTitle1[/TD]
[TD]Agent1[/TD]
[TD]8/5/2012[/TD]
[TD]FDR 16:30-17:45[/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD]JobTitle1[/TD]
[TD]Agent1[/TD]
[TD]8/5/2012[/TD]
[TD]Break 17:45-18:00[/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD]JobTitle1[/TD]
[TD]Agent1[/TD]
[TD]8/5/2012[/TD]
[TD]FDR 18:00-20:00[/TD]
[/TR]
[TR]
[TD="align: center"]15[/TD]
[TD]JobTitle1[/TD]
[TD]Agent1[/TD]
[TD]8/6/2012[/TD]
[TD]Technical Difficulty 09:30-10:10[/TD]
[/TR]
[TR]
[TD="align: center"]16[/TD]
[TD]JobTitle1[/TD]
[TD]Agent1[/TD]
[TD]8/6/2012[/TD]
[TD]Phone 10:10-10:45[/TD]
[/TR]
[TR]
[TD="align: center"]17[/TD]
[TD]JobTitle1[/TD]
[TD]Agent1[/TD]
[TD]8/6/2012[/TD]
[TD]Break 10:45-11:00[/TD]
[/TR]
[TR]
[TD="align: center"]18[/TD]
[TD]JobTitle1[/TD]
[TD]Agent1[/TD]
[TD]8/6/2012[/TD]
[TD]Phone 11:00-14:15[/TD]
[/TR]
[TR]
[TD="align: center"]19[/TD]
[TD]JobTitle1[/TD]
[TD]Agent1[/TD]
[TD]8/6/2012[/TD]
[TD]Lunch 14:15-14:45[/TD]
[/TR]
[TR]
[TD="align: center"]20[/TD]
[TD]JobTitle1[/TD]
[TD]Agent1[/TD]
[TD]8/6/2012[/TD]
[TD]Phone 14:45-16:45[/TD]
[/TR]
[TR]
[TD="align: center"]21[/TD]
[TD]JobTitle1[/TD]
[TD]Agent1[/TD]
[TD]8/6/2012[/TD]
[TD]Break 16:45-17:00[/TD]
[/TR]
[TR]
[TD="align: center"]22[/TD]
[TD]JobTitle1[/TD]
[TD]Agent1[/TD]
[TD]8/6/2012[/TD]
[TD]Phone 17:00-18:45[/TD]
[/TR]
[TR]
[TD="align: center"]23[/TD]
[TD]JobTitle1[/TD]
[TD]Agent1[/TD]
[TD]8/6/2012[/TD]
[TD]Break 18:45-19:00[/TD]
[/TR]
[TR]
[TD="align: center"]24[/TD]
[TD]JobTitle1[/TD]
[TD]Agent1[/TD]
[TD]8/6/2012[/TD]
[TD]Phone 19:00-20:00[/TD]
[/TR]
[TR]
[TD="align: center"]25[/TD]
[TD]JobTitle1[/TD]
[TD]Agent1[/TD]
[TD]8/7/2012[/TD]
[TD]Phone 09:30-11:30[/TD]
[/TR]
[TR]
[TD="align: center"]26[/TD]
[TD]JobTitle1[/TD]
[TD]Agent1[/TD]
[TD]8/7/2012[/TD]
[TD]Break 11:30-11:45[/TD]
[/TR]
[TR]
[TD="align: center"]27[/TD]
[TD]JobTitle1[/TD]
[TD]Agent1[/TD]
[TD]8/7/2012[/TD]
[TD]Phone 11:45-13:00[/TD]
[/TR]
[TR]
[TD="align: center"]28[/TD]
[TD]JobTitle1[/TD]
[TD]Agent1[/TD]
[TD]8/7/2012[/TD]
[TD]Lunch 13:00-13:30[/TD]
[/TR]
[TR]
[TD="align: center"]29[/TD]
[TD]JobTitle1[/TD]
[TD]Agent1[/TD]
[TD]8/7/2012[/TD]
[TD]Phone 13:30-16:00[/TD]
[/TR]
[TR]
[TD="align: center"]30[/TD]
[TD]JobTitle1[/TD]
[TD]Agent1[/TD]
[TD]8/7/2012[/TD]
[TD]Break 16:00-16:15[/TD]
[/TR]
[TR]
[TD="align: center"]31[/TD]
[TD]JobTitle1[/TD]
[TD]Agent1[/TD]
[TD]8/7/2012[/TD]
[TD]Phone 16:15-18:15[/TD]
[/TR]
[TR]
[TD="align: center"]32[/TD]
[TD]JobTitle1[/TD]
[TD]Agent1[/TD]
[TD]8/7/2012[/TD]
[TD]Break 18:15-18:30[/TD]
[/TR]
[TR]
[TD="align: center"]33[/TD]
[TD]JobTitle1[/TD]
[TD]Agent1[/TD]
[TD]8/7/2012[/TD]
[TD]Phone 18:30-20:00[/TD]
[/TR]
[TR]
[TD="align: center"]34[/TD]
[TD]JobTitle1[/TD]
[TD]Agent1[/TD]
[TD]8/8/2012[/TD]
[TD]FDR 09:30-11:15[/TD]
[/TR]
[TR]
[TD="align: center"]35[/TD]
[TD]JobTitle1[/TD]
[TD]Agent1[/TD]
[TD]8/8/2012[/TD]
[TD]Break 11:15-11:30[/TD]
[/TR]
[TR]
[TD="align: center"]36[/TD]
[TD]JobTitle1[/TD]
[TD]Agent1[/TD]
[TD]8/8/2012[/TD]
[TD]FDR 11:30-13:30[/TD]
[/TR]
[TR]
[TD="align: center"]37[/TD]
[TD]JobTitle1[/TD]
[TD]Agent1[/TD]
[TD]8/8/2012[/TD]
[TD]Lunch 13:30-14:00[/TD]
[/TR]
[TR]
[TD="align: center"]38[/TD]
[TD]JobTitle1[/TD]
[TD]Agent1[/TD]
[TD]8/8/2012[/TD]
[TD]FDR 14:00-15:30[/TD]
[/TR]
[TR]
[TD="align: center"]39[/TD]
[TD]JobTitle1[/TD]
[TD]Agent1[/TD]
[TD]8/8/2012[/TD]
[TD]Break 15:30-15:45[/TD]
[/TR]
[TR]
[TD="align: center"]40[/TD]
[TD]JobTitle1[/TD]
[TD]Agent1[/TD]
[TD]8/8/2012[/TD]
[TD]FDR 15:45-18:00[/TD]
[/TR]
[TR]
[TD="align: center"]41[/TD]
[TD]JobTitle1[/TD]
[TD]Agent1[/TD]
[TD]8/8/2012[/TD]
[TD]Break 18:00-18:15[/TD]
[/TR]
[TR]
[TD="align: center"]42[/TD]
[TD]JobTitle1[/TD]
[TD]Agent1[/TD]
[TD]8/8/2012[/TD]
[TD]Phone 18:15-20:00[/TD]
[/TR]
[TR]
[TD="align: center"]43[/TD]
[TD]JobTitle1[/TD]
[TD]Agent1[/TD]
[TD]8/9/2012[/TD]
[TD]Phone 11:00-14:00[/TD]
[/TR]
[TR]
[TD="align: center"]44[/TD]
[TD]JobTitle3[/TD]
[TD]Agent2[/TD]
[TD]8/7/2012[/TD]
[TD]PTO 12:30-14:15[/TD]
[/TR]
[TR]
[TD="align: center"]45[/TD]
[TD]JobTitle3[/TD]
[TD]Agent2[/TD]
[TD]8/7/2012[/TD]
[TD]PTO 14:15-14:30[/TD]
[/TR]
[TR]
[TD="align: center"]46[/TD]
[TD]JobTitle3[/TD]
[TD]Agent2[/TD]
[TD]8/7/2012[/TD]
[TD]PTO 14:30-16:15[/TD]
[/TR]
[TR]
[TD="align: center"]47[/TD]
[TD]JobTitle3[/TD]
[TD]Agent2[/TD]
[TD]8/7/2012[/TD]
[TD]PTO 16:15-16:45[/TD]
[/TR]
[TR]
[TD="align: center"]48[/TD]
[TD]JobTitle3[/TD]
[TD]Agent2[/TD]
[TD]8/7/2012[/TD]
[TD]PTO 16:45-18:30[/TD]
[/TR]
[TR]
[TD="align: center"]49[/TD]
[TD]JobTitle3[/TD]
[TD]Agent2[/TD]
[TD]8/7/2012[/TD]
[TD]PTO 18:30-18:45[/TD]
[/TR]
[TR]
[TD="align: center"]50[/TD]
[TD]JobTitle3[/TD]
[TD]Agent2[/TD]
[TD]8/7/2012[/TD]
[TD]PTO 18:45-20:45[/TD]
[/TR]
[TR]
[TD="align: center"]51[/TD]
[TD]JobTitle3[/TD]
[TD]Agent2[/TD]
[TD]8/7/2012[/TD]
[TD]PTO 20:45-21:00[/TD]
[/TR]
[TR]
[TD="align: center"]52[/TD]
[TD]JobTitle3[/TD]
[TD]Agent2[/TD]
[TD]8/7/2012[/TD]
[TD]PTO 21:00-23:00[/TD]
[/TR]
[TR]
[TD="align: center"]53[/TD]
[TD]JobTitle3[/TD]
[TD]Agent2[/TD]
[TD]8/9/2012[/TD]
[TD]Phone 12:30-13:45[/TD]
[/TR]
[TR]
[TD="align: center"]54[/TD]
[TD]JobTitle3[/TD]
[TD]Agent2[/TD]
[TD]8/9/2012[/TD]
[TD]Break 13:45-14:00[/TD]
[/TR]
[TR]
[TD="align: center"]55[/TD]
[TD]JobTitle3[/TD]
[TD]Agent2[/TD]
[TD]8/9/2012[/TD]
[TD]Phone 14:00-17:15[/TD]
[/TR]
[TR]
[TD="align: center"]56[/TD]
[TD]JobTitle3[/TD]
[TD]Agent2[/TD]
[TD]8/9/2012[/TD]
[TD]Lunch 17:15-17:45[/TD]
[/TR]
[TR]
[TD="align: center"]57[/TD]
[TD]JobTitle3[/TD]
[TD]Agent2[/TD]
[TD]8/9/2012[/TD]
[TD]Phone 17:45-19:00[/TD]
[/TR]
[TR]
[TD="align: center"]58[/TD]
[TD]JobTitle3[/TD]
[TD]Agent2[/TD]
[TD]8/9/2012[/TD]
[TD]Break 19:00-19:15[/TD]
[/TR]
[TR]
[TD="align: center"]59[/TD]
[TD]JobTitle3[/TD]
[TD]Agent2[/TD]
[TD]8/9/2012[/TD]
[TD]Phone 19:15-20:45[/TD]
[/TR]
[TR]
[TD="align: center"]60[/TD]
[TD]JobTitle3[/TD]
[TD]Agent2[/TD]
[TD]8/9/2012[/TD]
[TD]Break 20:45-21:00[/TD]
[/TR]
[TR]
[TD="align: center"]61[/TD]
[TD]JobTitle3[/TD]
[TD]Agent2[/TD]
[TD]8/9/2012[/TD]
[TD]Phone 21:00-23:00[/TD]
[/TR]
[TR]
[TD="align: center"]62[/TD]
[TD]JobTitle3[/TD]
[TD]Agent2[/TD]
[TD]8/10/2012[/TD]
[TD]Phone 08:30-09:45[/TD]
[/TR]
[TR]
[TD="align: center"]63[/TD]
[TD]JobTitle3[/TD]
[TD]Agent2[/TD]
[TD]8/10/2012[/TD]
[TD]Break 09:45-10:00[/TD]
[/TR]
[TR]
[TD="align: center"]64[/TD]
[TD]JobTitle3[/TD]
[TD]Agent2[/TD]
[TD]8/10/2012[/TD]
[TD]Phone 10:00-12:30[/TD]
[/TR]
[TR]
[TD="align: center"]65[/TD]
[TD]JobTitle3[/TD]
[TD]Agent2[/TD]
[TD]8/10/2012[/TD]
[TD]Lunch 12:30-13:00[/TD]
[/TR]
[TR]
[TD="align: center"]66[/TD]
[TD]JobTitle3[/TD]
[TD]Agent2[/TD]
[TD]8/10/2012[/TD]
[TD]Phone 13:00-14:45[/TD]
[/TR]
[TR]
[TD="align: center"]67[/TD]
[TD]JobTitle3[/TD]
[TD]Agent2[/TD]
[TD]8/10/2012[/TD]
[TD]Break 14:45-15:00[/TD]
[/TR]
[TR]
[TD="align: center"]68[/TD]
[TD]JobTitle3[/TD]
[TD]Agent2[/TD]
[TD]8/10/2012[/TD]
[TD]Phone 15:00-17:15[/TD]
[/TR]
[TR]
[TD="align: center"]69[/TD]
[TD]JobTitle3[/TD]
[TD]Agent2[/TD]
[TD]8/10/2012[/TD]
[TD]Break 17:15-17:30[/TD]
[/TR]
[TR]
[TD="align: center"]70[/TD]
[TD]JobTitle3[/TD]
[TD]Agent2[/TD]
[TD]8/10/2012[/TD]
[TD]Phone 17:30-19:00[/TD]
[/TR]
[TR]
[TD="align: center"]71[/TD]
[TD]JobTitle3[/TD]
[TD]Agent2[/TD]
[TD]8/11/2012[/TD]
[TD]Phone 09:30-10:45[/TD]
[/TR]
[TR]
[TD="align: center"]72[/TD]
[TD]JobTitle3[/TD]
[TD]Agent2[/TD]
[TD]8/11/2012[/TD]
[TD]Break 10:45-11:00[/TD]
[/TR]
[TR]
[TD="align: center"]73[/TD]
[TD]JobTitle3[/TD]
[TD]Agent2[/TD]
[TD]8/11/2012[/TD]
[TD]Phone 11:00-13:00[/TD]
[/TR]
[TR]
[TD="align: center"]74[/TD]
[TD]JobTitle3[/TD]
[TD]Agent2[/TD]
[TD]8/11/2012[/TD]
[TD]Lunch 13:00-13:30[/TD]
[/TR]
[TR]
[TD="align: center"]75[/TD]
[TD]JobTitle3[/TD]
[TD]Agent2[/TD]
[TD]8/11/2012[/TD]
[TD]Phone 13:30-15:30[/TD]
[/TR]
[TR]
[TD="align: center"]76[/TD]
[TD]JobTitle3[/TD]
[TD]Agent2[/TD]
[TD]8/11/2012[/TD]
[TD]Break 15:30-15:45[/TD]
[/TR]
[TR]
[TD="align: center"]77[/TD]
[TD]JobTitle3[/TD]
[TD]Agent2[/TD]
[TD]8/11/2012[/TD]
[TD]Phone 15:45-17:15[/TD]
[/TR]
[TR]
[TD="align: center"]78[/TD]
[TD]JobTitle3[/TD]
[TD]Agent2[/TD]
[TD]8/11/2012[/TD]
[TD]Break 17:15-17:30[/TD]
[/TR]
[TR]
[TD="align: center"]79[/TD]
[TD]JobTitle3[/TD]
[TD]Agent2[/TD]
[TD]8/11/2012[/TD]
[TD]Phone 17:30-20:00[/TD]
[/TR]
[TR]
[TD="align: center"]80[/TD]
[TD]JobTitle3[/TD]
[TD]Agent2[/TD]
[TD]8/14/2012[/TD]
[TD]Phone 12:30-15:00[/TD]
[/TR]
[TR]
[TD="align: center"]81[/TD]
[TD]JobTitle3[/TD]
[TD]Agent2[/TD]
[TD]8/14/2012[/TD]
[TD]Break 15:00-15:15[/TD]
[/TR]
[TR]
[TD="align: center"]82[/TD]
[TD]JobTitle3[/TD]
[TD]Agent2[/TD]
[TD]8/14/2012[/TD]
[TD]Phone 15:15-17:00[/TD]
[/TR]
[TR]
[TD="align: center"]83[/TD]
[TD]JobTitle3[/TD]
[TD]Agent2[/TD]
[TD]8/14/2012[/TD]
[TD]Lunch 17:00-17:30[/TD]
[/TR]
[TR]
[TD="align: center"]84[/TD]
[TD]JobTitle3[/TD]
[TD]Agent2[/TD]
[TD]8/14/2012[/TD]
[TD]Phone 17:30-19:30[/TD]
[/TR]
[TR]
[TD="align: center"]85[/TD]
[TD]JobTitle3[/TD]
[TD]Agent2[/TD]
[TD]8/14/2012[/TD]
[TD]Break 19:30-19:45[/TD]
[/TR]
[TR]
[TD="align: center"]86[/TD]
[TD]JobTitle3[/TD]
[TD]Agent2[/TD]
[TD]8/14/2012[/TD]
[TD]Phone 19:45-20:45[/TD]
[/TR]
[TR]
[TD="align: center"]87[/TD]
[TD]JobTitle3[/TD]
[TD]Agent2[/TD]
[TD]8/14/2012[/TD]
[TD]Break 20:45-21:00[/TD]
[/TR]
[TR]
[TD="align: center"]88[/TD]
[TD]JobTitle3[/TD]
[TD]Agent2[/TD]
[TD]8/14/2012[/TD]
[TD]Phone 21:00-23:00[/TD]
[/TR]
[TR]
[TD="align: center"]89[/TD]
[TD]JobTitle3[/TD]
[TD]Agent2[/TD]
[TD]8/15/2012[/TD]
[TD]Phone 12:30-14:00[/TD]
[/TR]
[TR]
[TD="align: center"]90[/TD]
[TD]JobTitle3[/TD]
[TD]Agent2[/TD]
[TD]8/15/2012[/TD]
[TD]Break 14:00-14:15[/TD]
[/TR]
[TR]
[TD="align: center"]91[/TD]
[TD]JobTitle3[/TD]
[TD]Agent2[/TD]
[TD]8/15/2012[/TD]
[TD]Phone 14:15-16:00[/TD]
[/TR]
[TR]
[TD="align: center"]92[/TD]
[TD]JobTitle3[/TD]
[TD]Agent2[/TD]
[TD]8/15/2012[/TD]
[TD]Lunch 16:00-16:30[/TD]
[/TR]
[TR]
[TD="align: center"]93[/TD]
[TD]JobTitle3[/TD]
[TD]Agent2[/TD]
[TD]8/15/2012[/TD]
[TD]Phone 16:30-19:15[/TD]
[/TR]
[TR]
[TD="align: center"]94[/TD]
[TD]JobTitle3[/TD]
[TD]Agent2[/TD]
[TD]8/15/2012[/TD]
[TD]Break 19:15-19:30[/TD]
[/TR]
[TR]
[TD="align: center"]95[/TD]
[TD]JobTitle3[/TD]
[TD]Agent2[/TD]
[TD]8/15/2012[/TD]
[TD]Phone 19:30-20:45[/TD]
[/TR]
[TR]
[TD="align: center"]96[/TD]
[TD]JobTitle3[/TD]
[TD]Agent2[/TD]
[TD]8/15/2012[/TD]
[TD]Break 20:45-21:00[/TD]
[/TR]
[TR]
[TD="align: center"]97[/TD]
[TD]JobTitle3[/TD]
[TD]Agent2[/TD]
[TD]8/15/2012[/TD]
[TD]Phone 21:00-23:00[/TD]
[/TR]
</tbody>[/TABLE]


This is how I would like the data to appear:

Excel 2007[TABLE="class: grid, width: 500"]
<tbody>[TR="bgcolor: #E0E0F0"]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[TH]C[/TH]
[TH]D[/TH]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]Job Title[/TD]
[TD]Name[/TD]
[TD]Date[/TD]
[TD]Activity[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]JobTitle1[/TD]
[TD]Agent1[/TD]
[TD]8/5/2012[/TD]
[TD]Phone 09:30-10:37
FDR 10:37-10:57
Phone 10:57-12:00
Break 12:00-12:15
Phone 12:15-13:06
FDR 13:06-13:31
Phone 13:31-14:00
Lunch 14:00-14:30
FDR 14:30-16:15
Break 16:15-16:30
FDR 16:30-17:45
Break 17:45-18:00
FDR 18:00-20:00[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]JobTitle1[/TD]
[TD]Agent1[/TD]
[TD]8/6/2012[/TD]
[TD]Technical Difficulty 09:30-10:10
Phone 10:10-10:45
Break 10:45-11:00
Phone 11:00-14:15
Lunch 14:15-14:45
Phone 14:45-16:45
Break 16:45-17:00
Phone 17:00-18:45
Break 18:45-19:00
Phone 19:00-20:00[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]JobTitle1[/TD]
[TD]Agent1[/TD]
[TD]8/7/2012[/TD]
[TD]Phone 09:30-11:30
Break 11:30-11:45
Phone 11:45-13:00
Lunch 13:00-13:30
Phone 13:30-16:00
Break 16:00-16:15
Phone 16:15-18:15
Break 18:15-18:30
Phone 18:30-20:00[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]JobTitle3[/TD]
[TD]Agent2[/TD]
[TD]8/7/2012[/TD]
[TD]PTO 12:30-14:15
PTO 14:15-14:30
PTO 14:30-16:15
PTO 16:15-16:45
PTO 16:45-18:30
PTO 18:30-18:45
PTO 18:45-20:45
PTO 20:45-21:00
PTO 21:00-23:00[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD]JobTitle1[/TD]
[TD]Agent1[/TD]
[TD]8/8/2012[/TD]
[TD]FDR 09:30-11:15
Break 11:15-11:30
FDR 11:30-13:30
Lunch 13:30-14:00
FDR 14:00-15:30
Break 15:30-15:45
FDR 15:45-18:00
Break 18:00-18:15
Phone 18:15-20:00
Phone 11:00-14:00[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD]JobTitle3[/TD]
[TD]Agent2[/TD]
[TD]8/9/2012[/TD]
[TD]Phone 12:30-13:45
Break 13:45-14:00
Phone 14:00-17:15
Lunch 17:15-17:45
Phone 17:45-19:00
Break 19:00-19:15
Phone 19:15-20:45
Break 20:45-21:00
Phone 21:00-23:00[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD]JobTitle3[/TD]
[TD]Agent2[/TD]
[TD]8/10/2012[/TD]
[TD]Phone 08:30-09:45
Break 09:45-10:00
Phone 10:00-12:30
Lunch 12:30-13:00
Phone 13:00-14:45
Break 14:45-15:00
Phone 15:00-17:15
Break 17:15-17:30
Phone 17:30-19:00[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD]JobTitle3[/TD]
[TD]Agent2[/TD]
[TD]8/11/2012[/TD]
[TD]Phone 09:30-10:45
Break 10:45-11:00
Phone 11:00-13:00
Lunch 13:00-13:30
Phone 13:30-15:30
Break 15:30-15:45
Phone 15:45-17:15
Break 17:15-17:30
Phone 17:30-20:00[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD]JobTitle1[/TD]
[TD]Agent1[/TD]
[TD]8/12/2012[/TD]
[TD]Phone 09:30-11:45
Break 11:45-12:00
Phone 12:00-14:00
Lunch 14:00-14:30
FDR 14:30-16:00
Break 16:00-16:15
FDR 16:15-17:30
Break 17:30-17:45
FDR 17:45-20:00[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD]JobTitle1[/TD]
[TD]Agent1[/TD]
[TD]8/13/2012[/TD]
[TD]Phone 09:30-11:15
Break 11:15-11:30
Phone 11:30-12:30
FDR 12:30-14:00
Lunch 14:00-14:30
FDR 14:30-16:00
Break 16:00-16:15
FDR 16:15-18:15
Break 18:15-18:30
FDR 18:30-20:00[/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD]JobTitle1[/TD]
[TD]Agent1[/TD]
[TD]8/14/2012[/TD]
[TD]Phone 09:30-11:30
Break 11:30-11:45
Phone 11:45-13:00
Lunch 13:00-13:30
Phone 13:30-16:45
Break 16:45-17:00
Phone 17:00-18:30
Break 18:30-18:45
Phone 18:45-20:00[/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD]JobTitle3[/TD]
[TD]Agent2[/TD]
[TD]8/14/2012[/TD]
[TD]Phone 12:30-15:00
Break 15:00-15:15
Phone 15:15-17:00
Lunch 17:00-17:30
Phone 17:30-19:30
Break 19:30-19:45
Phone 19:45-20:45
Break 20:45-21:00
Phone 21:00-23:00[/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD]JobTitle1[/TD]
[TD]Agent1[/TD]
[TD]8/15/2012[/TD]
[TD]Phone 09:30-12:00
Break 12:00-12:15
Phone 12:15-13:30
Lunch 13:30-14:00
Phone 14:00-16:00
Break 16:00-16:15
Phone 16:15-18:00
Break 18:00-18:15
Phone 18:15-20:00[/TD]
[/TR]
[TR]
[TD="align: center"]15[/TD]
[TD]JobTitle3[/TD]
[TD]Agent2[/TD]
[TD]8/15/2012[/TD]
[TD]Phone 12:30-14:00
Break 14:00-14:15
Phone 14:15-16:00
Lunch 16:00-16:30
Phone 16:30-19:15
Break 19:15-19:30
Phone 19:30-20:45
Break 20:45-21:00
Phone 21:00-23:00[/TD]
[/TR]
[TR]
[TD="align: center"]16[/TD]
[TD]JobTitle1[/TD]
[TD]Agent1[/TD]
[TD]8/16/2012[/TD]
[TD]Phone 11:00-14:00[/TD]
[/TR]
[TR]
[TD="align: center"]17[/TD]
[TD]JobTitle3[/TD]
[TD]Agent2[/TD]
[TD]8/16/2012[/TD]
[TD]Phone 12:30-14:15
Break 14:15-14:30
Phone 14:30-17:15
Lunch 17:15-17:45
Phone 17:45-19:00
Break 19:00-19:15
Phone 19:15-20:45
Break 20:45-21:00
Phone 21:00-23:00[/TD]
[/TR]
[TR]
[TD="align: center"]18[/TD]
[TD]JobTitle3[/TD]
[TD]Agent2[/TD]
[TD]8/17/2012[/TD]
[TD]Phone 12:30-14:45
Break 14:45-15:00
Phone 15:00-17:00
Lunch 17:00-17:30
Phone 17:30-19:15
Break 19:15-19:30
Phone 19:30-20:45
Break 20:45-21:00
Phone 21:00-23:00[/TD]
[/TR]
[TR]
[TD="align: center"]19[/TD]
[TD]JobTitle1[/TD]
[TD]Agent1[/TD]
[TD]8/19/2012[/TD]
[TD]Phone 09:30-11:45
Break 11:45-12:00
Phone 12:00-14:00
Lunch 14:00-14:30
FDR 14:30-16:00
Break 16:00-16:15
FDR 16:15-17:45
Break 17:45-18:00
Phone 18:00-20:00[/TD]
[/TR]
[TR]
[TD="align: center"]20[/TD]
[TD]JobTitle1[/TD]
[TD]Agent1[/TD]
[TD]8/20/2012[/TD]
[TD]Phone 09:30-11:00
Break 11:00-11:15
Phone 11:15-12:30
FDR 12:30-14:00
Lunch 14:00-14:30
FDR 14:30-16:00
Break 16:00-16:15
FDR 16:15-18:15
Break 18:15-18:30
FDR 18:30-20:00[/TD]
[/TR]
[TR]
[TD="align: center"]21[/TD]
[TD]JobTitle1[/TD]
[TD]Agent1[/TD]
[TD]8/21/2012[/TD]
[TD]Phone 09:30-11:30
Break 11:30-11:45
Phone 11:45-14:15
Lunch 14:15-14:45
Phone 14:45-16:45
Break 16:45-17:00
Phone 17:00-18:30
Break 18:30-18:45
Phone 18:45-20:00[/TD]
[/TR]
[TR]
[TD="align: center"]22[/TD]
[TD]JobTitle3[/TD]
[TD]Agent2[/TD]
[TD]8/21/2012[/TD]
[TD]Phone 12:30-15:00
Break 15:00-15:15
Phone 15:15-16:45
Lunch 16:45-17:15
Phone 17:15-19:30
Break 19:30-19:45
Phone 19:45-20:45
Break 20:45-21:00
Phone 21:00-23:00
Break 18:45-19:00[/TD]
[/TR]
[TR]
[TD="align: center"]23[/TD]
[TD]JobTitle1[/TD]
[TD]Agent1[/TD]
[TD]8/22/2012[/TD]
[TD]Phone 09:30-11:30
Break 11:30-11:45
Phone 11:45-13:15
Lunch 13:15-13:45
Phone 13:45-15:45
Break 15:45-16:00
Phone 16:00-18:45
Break 18:45-19:00
Phone 19:00-20:00[/TD]
[/TR]
[TR]
[TD="align: center"]24[/TD]
[TD]JobTitle3[/TD]
[TD]Agent2[/TD]
[TD]8/22/2012[/TD]
[TD]Phone 12:30-14:00
Break 14:00-14:15
Phone 14:15-16:15
Lunch 16:15-16:45
Phone 16:45-19:15
Break 19:15-19:30
Phone 19:30-20:45
Break 20:45-21:00
Phone 21:00-23:00[/TD]
[/TR]
[TR]
[TD="align: center"]25[/TD]
[TD]JobTitle1[/TD]
[TD]Agent1[/TD]
[TD]8/23/2012[/TD]
[TD]Phone 12:00-13:30
Break 13:30-13:45
Phone 13:45-16:00[/TD]
[/TR]
[TR]
[TD="align: center"]26[/TD]
[TD]JobTitle3[/TD]
[TD]Agent2[/TD]
[TD]8/23/2012[/TD]
[TD]Phone 12:30-14:45
Break 14:45-15:00
Phone 15:00-17:15
Lunch 17:15-17:45
Phone 17:45-19:45
Break 19:45-20:00
Phone 20:00-21:15
Break 21:15-21:30
Phone 21:30-23:00[/TD]
[/TR]
[TR]
[TD="align: center"]27[/TD]
[TD]JobTitle3[/TD]
[TD]Agent2[/TD]
[TD]8/24/2012[/TD]
[TD]Phone 12:30-14:30
Break 14:30-14:45
Phone 14:45-16:45
Lunch 16:45-17:15
Phone 17:15-19:15
Break 19:15-19:30
Phone 19:30-20:45
Break 20:45-21:00
Phone 21:00-23:00[/TD]
[/TR]
[TR]
[TD="align: center"]28[/TD]
[TD]JobTitle1[/TD]
[TD]Agent1[/TD]
[TD]8/26/2012[/TD]
[TD]Phone 09:30-12:00
Break 12:00-12:15
Phone 12:15-14:00
Lunch 14:00-14:30
FDR 14:30-15:45
Break 15:45-16:00
FDR 16:00-17:30
Break 17:30-17:45
FDR 17:45-20:00[/TD]
[/TR]
[TR]
[TD="align: center"]29[/TD]
[TD]JobTitle1[/TD]
[TD]Agent1[/TD]
[TD]8/27/2012[/TD]
[TD]Phone 09:30-10:45
Break 10:45-11:00
Phone 11:00-14:15
Lunch 14:15-14:45
Phone 14:45-16:00
Break 16:00-16:15
Phone 16:15-17:45
Break 17:45-18:00
Phone 18:00-20:00[/TD]
[/TR]
[TR]
[TD="align: center"]30[/TD]
[TD]JobTitle1[/TD]
[TD]Agent1[/TD]
[TD]8/28/2012[/TD]
[TD]Phone 09:30-12:00
Break 12:00-12:15
Phone 12:15-14:15
Lunch 14:15-14:45
Phone 14:45-16:45
Break 16:45-17:00
Phone 17:00-18:00
Break 18:00-18:15
Phone 18:15-20:00
FDR 18:00-20:00[/TD]
[/TR]
[TR]
[TD="align: center"]31[/TD]
[TD]JobTitle3[/TD]
[TD]Agent2[/TD]
[TD]8/28/2012[/TD]
[TD]Phone 12:30-14:45
Break 14:45-15:00
Phone 15:00-17:15
Lunch 17:15-17:45
Phone 17:45-20:30
Break 20:30-20:45
Phone 20:45-22:00
PTO 22:00-23:00[/TD]
[/TR]
</tbody>[/TABLE]

There could be up to 42 dates per agents, as many as 90 agents, and a varying number of activities for each date.

I'm struggling with how to separate the different dates and the alt+enter break to keep the actives on their one line within the cells.

Thank you for reading, my apologies for the large post.

 

[lrobbo314]

I have a solution.

First, reverse the order of your entries. This can be done by creating a helper column. Number the entries from 1 to whatever and then sort them from largest to smallest.

Then, run this code.

Sub test()
Dim LR As Long
Dim str As String
LR = Range("C" & Rows.Count).End(xlUp).Row()
For LR = LR To 2 Step -1
    If Cells(LR, 3) = Cells(LR - 1, 3) Then
        str = str & Cells(LR, 4) & vbCrLf
        Cells(LR, 3).Rows.EntireRow.Delete
    Else
        str = str & Cells(LR, 4) & vbCrLf
            
        Cells(LR, 4).Value = str
        str = vbNullString
    End If
Next LR
            
End Sub

After you've dont that, delete the helper column and then do another sort.

This time by Job Title Then by Date.

Let me know if you need any help with getting that done.

 

[lrobbo314]

Or this will do it without the sorting to do along with it. Just run the code.

Sub test()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim LR As Long
Dim str As String, temp As String
Dim sp() As String
Dim x As Integer, y As Integer
LR = Range("C" & Rows.Count).End(xlUp).Row()
For LR = LR To 2 Step -1
    If Cells(LR, 3) = Cells(LR - 1, 3) Then
        str = str & Cells(LR, 4) & vbCrLf
        Cells(LR, 3).Rows.EntireRow.Delete
    Else
        str = str & Cells(LR, 4) & vbCrLf
        str = Left(str, Len(str) - 2)
        sp = Split(str, Chr(13))
        str = vbNullString
            For x = LBound(sp) To UBound(sp)
                For y = x To UBound(sp)
                    If x < y Then
                        temp = Trim(Replace(sp(x), Chr(10), vbNullString))
                        sp(x) = Trim(Replace(sp(y), Chr(10), vbNullString))
                        sp(y) = Trim(Replace(temp, Chr(10), vbNullString))
                    End If
                Next y
            Next x
            
            For x = LBound(sp) To UBound(sp)
                str = str & sp(x) & vbCrLf
            Next x
                  
        Cells(LR, 4).Value = Left(str, Len(str) - 2)
        str = vbNullString
    End If
Next LR
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

 

[GMLee]

Thank you very much! I'm testing this out now. I will respond once it is all working as well, though may be a couple hours!

 

[lrobbo314]

Cool, let me know!

 

[GMLee]

I used the second bit of code. It is perfect. Thank you Irobbo314!

The Activitis end up with a question mark in a square at the end, I'm guessing due to the break. It there any way to avoid that?

 

[lrobbo314]

I don't see any question marks after I run the code off of the sample data. What do you mean by breaks? Breaks as an activity, or breaks in the data?

 

[GMLee]

I uploaded an image to displayimgur: the simple image sharer

 

[lrobbo314]

I'll have to defer that one to another member. It might be a setting or a weird font that you r using, but for some reason it is showing your carriage returns as question marks.

 

[GMLee]

Hello,

I'm wondering how I would edit this code to not add the breaks, so all the data is on one row?

Or this will do it without the sorting to do along with it. Just run the code.

Sub test()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim LR As Long
Dim str As String, temp As String
Dim sp() As String
Dim x As Integer, y As Integer
LR = Range("C" & Rows.Count).End(xlUp).Row()
For LR = LR To 2 Step -1
    If Cells(LR, 3) = Cells(LR - 1, 3) Then
        str = str & Cells(LR, 4) & vbCrLf
        Cells(LR, 3).Rows.EntireRow.Delete
    Else
        str = str & Cells(LR, 4) & vbCrLf
        str = Left(str, Len(str) - 2)
        sp = Split(str, Chr(13))
        str = vbNullString
            For x = LBound(sp) To UBound(sp)
                For y = x To UBound(sp)
                    If x < y Then
                        temp = Trim(Replace(sp(x), Chr(10), vbNullString))
                        sp(x) = Trim(Replace(sp(y), Chr(10), vbNullString))
                        sp(y) = Trim(Replace(temp, Chr(10), vbNullString))
                    End If
                Next y
            Next x
            
            For x = LBound(sp) To UBound(sp)
                str = str & sp(x) & vbCrLf
            Next x
                  
        Cells(LR, 4).Value = Left(str, Len(str) - 2)
        str = vbNullString
    End If
Next LR
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub