leeksleeks
Board Regular
- Joined
- Oct 31, 2013
- Messages
- 96
Hi,
I need to be able to summarise the table below to show who was doing what hour by hour so that this can be used in a database. Essentially I need to extract an hour by hour view from the large table below to just return this:
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]08:00[/TD]
[TD]name.1[/TD]
[TD]SALES 2[/TD]
[/TR]
[TR]
[TD]08:00[/TD]
[TD]name.4[/TD]
[TD]TRAINING[/TD]
[/TR]
[TR]
[TD]08:00[/TD]
[TD]name.6[/TD]
[TD]SALES 1[/TD]
[/TR]
[TR]
[TD]08:30[/TD]
[TD]name.1[/TD]
[TD]SALES 2[/TD]
[/TR]
</tbody>[/TABLE]
This is the table that I want the data to be summarised from. Is there some VBA code that can extract the time, name and task from each of the lines where there is data on it? So if there is a task assigned, return the time of that task and the name at the top of the column.
[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="class: grid, width: 720, align: center"]
<colgroup><col span="3"><col><col span="2"><col><col><col></colgroup><tbody>[TR]
[TD] [/TD]
[TD]name.1[/TD]
[TD]name.2[/TD]
[TD]name.3[/TD]
[TD]name.4[/TD]
[TD]name.5[/TD]
[TD]name.6[/TD]
[TD]name.7[/TD]
[TD]name.8[/TD]
[/TR]
[TR]
[TD]08:00[/TD]
[TD]SALES 2[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]TRAINING[/TD]
[TD] [/TD]
[TD]SALES 1[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]08:30[/TD]
[TD]SALES 2[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]TRAINING[/TD]
[TD] [/TD]
[TD]SALES 1[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]09:00[/TD]
[TD]SALES 2[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]TRAINING[/TD]
[TD] [/TD]
[TD]SALES 1[/TD]
[TD] [/TD]
[TD]TRAINING[/TD]
[/TR]
[TR]
[TD]09:30[/TD]
[TD]SALES 2[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]TRAINING[/TD]
[TD] [/TD]
[TD]SALES 1[/TD]
[TD] [/TD]
[TD]TRAINING[/TD]
[/TR]
[TR]
[TD]10:00[/TD]
[TD]SALES 2[/TD]
[TD] [/TD]
[TD]SALES 1[/TD]
[TD]TRAINING[/TD]
[TD] [/TD]
[TD]SALES 4[/TD]
[TD]SALES 5[/TD]
[TD]SALES 6[/TD]
[/TR]
[TR]
[TD]10:30[/TD]
[TD]SALES 2[/TD]
[TD] [/TD]
[TD]SALES 1[/TD]
[TD]TRAINING[/TD]
[TD] [/TD]
[TD]SALES 4[/TD]
[TD]SALES 5[/TD]
[TD]SALES 6[/TD]
[/TR]
[TR]
[TD]11:00[/TD]
[TD]SALES 2[/TD]
[TD] [/TD]
[TD]SALES 1[/TD]
[TD]TRAINING[/TD]
[TD] [/TD]
[TD]SALES 4[/TD]
[TD]SALES 5[/TD]
[TD]TRAINING[/TD]
[/TR]
[TR]
[TD]11:30[/TD]
[TD]SALES 2[/TD]
[TD] [/TD]
[TD]SALES 1[/TD]
[TD]TRAINING[/TD]
[TD] [/TD]
[TD]SALES 4[/TD]
[TD]SALES 5[/TD]
[TD]TRAINING[/TD]
[/TR]
[TR]
[TD]12:00[/TD]
[TD]SALES 2[/TD]
[TD] [/TD]
[TD]SALES 1[/TD]
[TD]TRAINING[/TD]
[TD] [/TD]
[TD]SALES 4[/TD]
[TD]SALES 4[/TD]
[TD]TRAINING[/TD]
[/TR]
[TR]
[TD]12:30[/TD]
[TD]SALES 2[/TD]
[TD] [/TD]
[TD]SALES 1[/TD]
[TD]TRAINING[/TD]
[TD] [/TD]
[TD]OOF[/TD]
[TD]SALES 4[/TD]
[TD]TRAINING[/TD]
[/TR]
[TR]
[TD]13:00[/TD]
[TD]SALES 2[/TD]
[TD]SALES 2[/TD]
[TD]SALES 1[/TD]
[TD]TRAINING[/TD]
[TD]SALES 1[/TD]
[TD]OOF[/TD]
[TD]SALES 4[/TD]
[TD]TRAINING[/TD]
[/TR]
[TR]
[TD]13:30[/TD]
[TD]SALES 4[/TD]
[TD]SALES 2[/TD]
[TD]SALES 1[/TD]
[TD]TRAINING[/TD]
[TD]SALES 1[/TD]
[TD]OOF[/TD]
[TD]SALES 4[/TD]
[TD]TRAINING[/TD]
[/TR]
[TR]
[TD]14:00[/TD]
[TD]SALES 4[/TD]
[TD]MEETING[/TD]
[TD]SALES 1[/TD]
[TD]TRAINING[/TD]
[TD]MEETING[/TD]
[TD]SALES 2[/TD]
[TD]SALES 2[/TD]
[TD]TRAINING[/TD]
[/TR]
[TR]
[TD]14:30[/TD]
[TD]SALES 4[/TD]
[TD]MEETING[/TD]
[TD]SALES 1[/TD]
[TD]TRAINING[/TD]
[TD]MEETING[/TD]
[TD]SALES 2[/TD]
[TD]SALES 2[/TD]
[TD]TRAINING[/TD]
[/TR]
[TR]
[TD]15:00[/TD]
[TD]SALES 4[/TD]
[TD]MEETING[/TD]
[TD]SALES 1[/TD]
[TD]TRAINING[/TD]
[TD]MEETING[/TD]
[TD]SALES 2[/TD]
[TD]SALES 2[/TD]
[TD]TRAINING[/TD]
[/TR]
[TR]
[TD]15:30[/TD]
[TD]SALES 4[/TD]
[TD]MEETING[/TD]
[TD]SALES 1[/TD]
[TD]TRAINING[/TD]
[TD]MEETING[/TD]
[TD]SALES 2[/TD]
[TD]SALES 2[/TD]
[TD]TRAINING[/TD]
[/TR]
[TR]
[TD]16:00[/TD]
[TD]SALES 4[/TD]
[TD]MEETING[/TD]
[TD]SALES 1[/TD]
[TD]TRAINING[/TD]
[TD]MEETING[/TD]
[TD]SALES 2[/TD]
[TD]SALES 2[/TD]
[TD]TRAINING[/TD]
[/TR]
[TR]
[TD]16:30[/TD]
[TD] [/TD]
[TD]SALES 4[/TD]
[TD]SALES 1[/TD]
[TD] [/TD]
[TD]TRAINING[/TD]
[TD] [/TD]
[TD]SALES 2[/TD]
[TD]TRAINING[/TD]
[/TR]
[TR]
[TD]17:00[/TD]
[TD] [/TD]
[TD]SALES 4[/TD]
[TD]SALES 1[/TD]
[TD] [/TD]
[TD]TRAINING[/TD]
[TD] [/TD]
[TD]SALES 2[/TD]
[TD]TRAINING[/TD]
[/TR]
[TR]
[TD]17:30[/TD]
[TD] [/TD]
[TD]SALES 4[/TD]
[TD]SALES 1[/TD]
[TD] [/TD]
[TD]TRAINING[/TD]
[TD] [/TD]
[TD]SALES 2[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]18:00[/TD]
[TD] [/TD]
[TD]SALES 4[/TD]
[TD]SALES 1[/TD]
[TD] [/TD]
[TD]TRAINING[/TD]
[TD] [/TD]
[TD]SALES 2[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]18:30[/TD]
[TD] [/TD]
[TD]SALES 2[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]SALES 1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]19:00[/TD]
[TD] [/TD]
[TD]SALES 2[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]SALES 1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]19:30[/TD]
[TD] [/TD]
[TD]SALES 2[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]SALES 1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]20:00[/TD]
[TD] [/TD]
[TD]SALES 2[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]SALES 1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]20:30[/TD]
[TD] [/TD]
[TD]SALES 2[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]SALES 1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]21:00[/TD]
[TD] [/TD]
[TD]SALES 2[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]SALES 1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]21:30[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I need to be able to summarise the table below to show who was doing what hour by hour so that this can be used in a database. Essentially I need to extract an hour by hour view from the large table below to just return this:
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]08:00[/TD]
[TD]name.1[/TD]
[TD]SALES 2[/TD]
[/TR]
[TR]
[TD]08:00[/TD]
[TD]name.4[/TD]
[TD]TRAINING[/TD]
[/TR]
[TR]
[TD]08:00[/TD]
[TD]name.6[/TD]
[TD]SALES 1[/TD]
[/TR]
[TR]
[TD]08:30[/TD]
[TD]name.1[/TD]
[TD]SALES 2[/TD]
[/TR]
</tbody>[/TABLE]
This is the table that I want the data to be summarised from. Is there some VBA code that can extract the time, name and task from each of the lines where there is data on it? So if there is a task assigned, return the time of that task and the name at the top of the column.
[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="class: grid, width: 720, align: center"]
<colgroup><col span="3"><col><col span="2"><col><col><col></colgroup><tbody>[TR]
[TD] [/TD]
[TD]name.1[/TD]
[TD]name.2[/TD]
[TD]name.3[/TD]
[TD]name.4[/TD]
[TD]name.5[/TD]
[TD]name.6[/TD]
[TD]name.7[/TD]
[TD]name.8[/TD]
[/TR]
[TR]
[TD]08:00[/TD]
[TD]SALES 2[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]TRAINING[/TD]
[TD] [/TD]
[TD]SALES 1[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]08:30[/TD]
[TD]SALES 2[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]TRAINING[/TD]
[TD] [/TD]
[TD]SALES 1[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]09:00[/TD]
[TD]SALES 2[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]TRAINING[/TD]
[TD] [/TD]
[TD]SALES 1[/TD]
[TD] [/TD]
[TD]TRAINING[/TD]
[/TR]
[TR]
[TD]09:30[/TD]
[TD]SALES 2[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]TRAINING[/TD]
[TD] [/TD]
[TD]SALES 1[/TD]
[TD] [/TD]
[TD]TRAINING[/TD]
[/TR]
[TR]
[TD]10:00[/TD]
[TD]SALES 2[/TD]
[TD] [/TD]
[TD]SALES 1[/TD]
[TD]TRAINING[/TD]
[TD] [/TD]
[TD]SALES 4[/TD]
[TD]SALES 5[/TD]
[TD]SALES 6[/TD]
[/TR]
[TR]
[TD]10:30[/TD]
[TD]SALES 2[/TD]
[TD] [/TD]
[TD]SALES 1[/TD]
[TD]TRAINING[/TD]
[TD] [/TD]
[TD]SALES 4[/TD]
[TD]SALES 5[/TD]
[TD]SALES 6[/TD]
[/TR]
[TR]
[TD]11:00[/TD]
[TD]SALES 2[/TD]
[TD] [/TD]
[TD]SALES 1[/TD]
[TD]TRAINING[/TD]
[TD] [/TD]
[TD]SALES 4[/TD]
[TD]SALES 5[/TD]
[TD]TRAINING[/TD]
[/TR]
[TR]
[TD]11:30[/TD]
[TD]SALES 2[/TD]
[TD] [/TD]
[TD]SALES 1[/TD]
[TD]TRAINING[/TD]
[TD] [/TD]
[TD]SALES 4[/TD]
[TD]SALES 5[/TD]
[TD]TRAINING[/TD]
[/TR]
[TR]
[TD]12:00[/TD]
[TD]SALES 2[/TD]
[TD] [/TD]
[TD]SALES 1[/TD]
[TD]TRAINING[/TD]
[TD] [/TD]
[TD]SALES 4[/TD]
[TD]SALES 4[/TD]
[TD]TRAINING[/TD]
[/TR]
[TR]
[TD]12:30[/TD]
[TD]SALES 2[/TD]
[TD] [/TD]
[TD]SALES 1[/TD]
[TD]TRAINING[/TD]
[TD] [/TD]
[TD]OOF[/TD]
[TD]SALES 4[/TD]
[TD]TRAINING[/TD]
[/TR]
[TR]
[TD]13:00[/TD]
[TD]SALES 2[/TD]
[TD]SALES 2[/TD]
[TD]SALES 1[/TD]
[TD]TRAINING[/TD]
[TD]SALES 1[/TD]
[TD]OOF[/TD]
[TD]SALES 4[/TD]
[TD]TRAINING[/TD]
[/TR]
[TR]
[TD]13:30[/TD]
[TD]SALES 4[/TD]
[TD]SALES 2[/TD]
[TD]SALES 1[/TD]
[TD]TRAINING[/TD]
[TD]SALES 1[/TD]
[TD]OOF[/TD]
[TD]SALES 4[/TD]
[TD]TRAINING[/TD]
[/TR]
[TR]
[TD]14:00[/TD]
[TD]SALES 4[/TD]
[TD]MEETING[/TD]
[TD]SALES 1[/TD]
[TD]TRAINING[/TD]
[TD]MEETING[/TD]
[TD]SALES 2[/TD]
[TD]SALES 2[/TD]
[TD]TRAINING[/TD]
[/TR]
[TR]
[TD]14:30[/TD]
[TD]SALES 4[/TD]
[TD]MEETING[/TD]
[TD]SALES 1[/TD]
[TD]TRAINING[/TD]
[TD]MEETING[/TD]
[TD]SALES 2[/TD]
[TD]SALES 2[/TD]
[TD]TRAINING[/TD]
[/TR]
[TR]
[TD]15:00[/TD]
[TD]SALES 4[/TD]
[TD]MEETING[/TD]
[TD]SALES 1[/TD]
[TD]TRAINING[/TD]
[TD]MEETING[/TD]
[TD]SALES 2[/TD]
[TD]SALES 2[/TD]
[TD]TRAINING[/TD]
[/TR]
[TR]
[TD]15:30[/TD]
[TD]SALES 4[/TD]
[TD]MEETING[/TD]
[TD]SALES 1[/TD]
[TD]TRAINING[/TD]
[TD]MEETING[/TD]
[TD]SALES 2[/TD]
[TD]SALES 2[/TD]
[TD]TRAINING[/TD]
[/TR]
[TR]
[TD]16:00[/TD]
[TD]SALES 4[/TD]
[TD]MEETING[/TD]
[TD]SALES 1[/TD]
[TD]TRAINING[/TD]
[TD]MEETING[/TD]
[TD]SALES 2[/TD]
[TD]SALES 2[/TD]
[TD]TRAINING[/TD]
[/TR]
[TR]
[TD]16:30[/TD]
[TD] [/TD]
[TD]SALES 4[/TD]
[TD]SALES 1[/TD]
[TD] [/TD]
[TD]TRAINING[/TD]
[TD] [/TD]
[TD]SALES 2[/TD]
[TD]TRAINING[/TD]
[/TR]
[TR]
[TD]17:00[/TD]
[TD] [/TD]
[TD]SALES 4[/TD]
[TD]SALES 1[/TD]
[TD] [/TD]
[TD]TRAINING[/TD]
[TD] [/TD]
[TD]SALES 2[/TD]
[TD]TRAINING[/TD]
[/TR]
[TR]
[TD]17:30[/TD]
[TD] [/TD]
[TD]SALES 4[/TD]
[TD]SALES 1[/TD]
[TD] [/TD]
[TD]TRAINING[/TD]
[TD] [/TD]
[TD]SALES 2[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]18:00[/TD]
[TD] [/TD]
[TD]SALES 4[/TD]
[TD]SALES 1[/TD]
[TD] [/TD]
[TD]TRAINING[/TD]
[TD] [/TD]
[TD]SALES 2[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]18:30[/TD]
[TD] [/TD]
[TD]SALES 2[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]SALES 1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]19:00[/TD]
[TD] [/TD]
[TD]SALES 2[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]SALES 1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]19:30[/TD]
[TD] [/TD]
[TD]SALES 2[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]SALES 1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]20:00[/TD]
[TD] [/TD]
[TD]SALES 2[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]SALES 1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]20:30[/TD]
[TD] [/TD]
[TD]SALES 2[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]SALES 1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]21:00[/TD]
[TD] [/TD]
[TD]SALES 2[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]SALES 1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]21:30[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]