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