Summarise cells - row by row

[leeksleeks]

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]

 

[leeksleeks]

I have tried both yours and Peter_SSs and they both work perfectly. Thank you for your work here. It has saved me hours of banging my head against my desk!

 

[Peter_SSs]

I have tried both yours and Peter_SSs and they both work perfectly.

Good news - you have choice! Thanks for letting us know.

 

[sandy666]

You are welcome

Have a nice day