Best way to clean up this mess?

[crazybuckeyeguy]

I have this data for about 500 people and the program we use exports it like this in about 15000 rows. I am trying to consolidate it so it can be used in a another sheet as a vlookup. I essentially need something to put all totals in 1 line or to change the name of Subtotal to the agents name that would be 2 cells above it. any ideas how to clean this up easier. I will need to do it once a week and if I could just paste the data and run a macro it would save me so much time.





[TABLE="width: 825"]
<tbody>[TR]
[TD]Agent Name[/TD]
[TD]Queue/VQ[/TD]
[TD]Calls Inbound[/TD]
[TD]Week[/TD]
[TD]Avg Handle Time[/TD]
[TD]Talk Time[/TD]
[TD]Talk Time[/TD]
[TD]Hold Time[/TD]
[TD]Hold Time[/TD]
[TD]ACW Time[/TD]
[TD]ACW Time[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Total[/TD]
[TD]Avg[/TD]
[TD]Total[/TD]
[TD]Avg[/TD]
[TD]Total[/TD]
[TD]Avg[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD](yabrego)[/TD]
[TD]VQ_FLIFO[/TD]
[TD]5[/TD]
[TD]2017-30[/TD]
[TD]00:02:48[/TD]
[TD]00:10:44[/TD]
[TD]00:02:09[/TD]
[TD]00:03:18[/TD]
[TD]00:00:40[/TD]
[TD]00:00:00[/TD]
[TD]00:00:00[/TD]
[/TR]
[TR]
[TD](yabrego)[/TD]
[TD]VQ_GENRES[/TD]
[TD]218[/TD]
[TD]2017-30[/TD]
[TD]00:05:57[/TD]
[TD]18:32:07[/TD]
[TD]00:05:01[/TD]
[TD]02:21:57[/TD]
[TD]00:00:39[/TD]
[TD]01:03:59[/TD]
[TD]00:00:17[/TD]
[/TR]
[TR]
[TD](yabrego)[/TD]
[TD]VQ_GENRES_SPAN[/TD]
[TD]18[/TD]
[TD]2017-30[/TD]
[TD]00:06:48[/TD]
[TD]01:51:19[/TD]
[TD]00:05:55[/TD]
[TD]00:11:29[/TD]
[TD]00:00:38[/TD]
[TD]00:05:28[/TD]
[TD]00:00:17[/TD]
[/TR]
[TR]
[TD](yabrego)[/TD]
[TD]VQ_MVP[/TD]
[TD]54[/TD]
[TD]2017-30[/TD]
[TD]00:07:51[/TD]
[TD]05:51:10[/TD]
[TD]00:06:24[/TD]
[TD]00:48:58[/TD]
[TD]00:00:54[/TD]
[TD]00:30:23[/TD]
[TD]00:00:33[/TD]
[/TR]
[TR]
[TD](yabrego)[/TD]
[TD]VQ_NONREV[/TD]
[TD]1[/TD]
[TD]2017-30[/TD]
[TD]00:01:52[/TD]
[TD]00:01:52[/TD]
[TD]00:01:52[/TD]
[TD]00:00:00[/TD]
[TD]00:00:00[/TD]
[TD]00:00:00[/TD]
[TD]00:00:00[/TD]
[/TR]
[TR]
[TD](yabrego)[/TD]
[TD]VQ_PARTNER[/TD]
[TD]26[/TD]
[TD]2017-30[/TD]
[TD]00:09:02[/TD]
[TD]03:46:44[/TD]
[TD]00:08:43[/TD]
[TD]00:01:05[/TD]
[TD]00:00:03[/TD]
[TD]00:07:05[/TD]
[TD]00:00:16[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]SUB TOTAL:[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]00:06:30[/TD]
[TD]30:13:56[/TD]
[TD]00:05:33[/TD]
[TD]03:26:47[/TD]
[TD]00:00:39[/TD]
[TD]01:46:55[/TD]
[TD]00:00:20[/TD]
[/TR]
</tbody><colgroup><col span="2"><col><col><col><col><col><col><col><col><col></colgroup>[/TABLE]

 

[onlyadrafter]

Hello,

have assumed the download is in Excel and the data starts in Column A.

Sub subtotal()
    Application.ScreenUpdating = False
    For MY_ROWS = 1 To Range("A" & Rows.Count).End(xlUp).Row
        If Range("A" & MY_ROWS).Value = "SUB TOTAL:" Then
            Range("A" & MY_ROWS).Value = Range("A" & MY_ROWS - 2).Value
        End If
    Next MY_ROWS
    Application.ScreenUpdating = True
End Sub

This code replaces "SUB TOTAL:" with the cell two rows above.

 

[excelltrainee]

A non-macro solution could be to create a template where you would paste the raw data into. Since the system spits out the data the same way each time (columns from left to right) then a copy paste into a template that contained the sum functions, would auto calculate and give you the output you would want.

 

[crazybuckeyeguy]

A non-macro solution could be to create a template where you would paste the raw data into. Since the system spits out the data the same way each time (columns from left to right) then a copy paste into a template that contained the sum functions, would auto calculate and give you the output you would want.

That's a great idea however the report can change with attrition of agents so it wouldn't always be the same forever. I tried a pivot table but having to run the pivot table report weekly was time consuming. if I can change the name of subtotal then I can clean up the sheet and it can be pasted into a template that will copy and past values. Im definitely struggling on this and my boss has given me 2 weeks to improve this process we have.

 

[tonyyy]

crazybuckeyeguy,

If all you want to do is to rename the "SUB TOTAL:" cell to the agents' name, then @onlyadrafter's code will do that. If, however, you want to run vlookup functions or create PivotTables against this, you might want to consider deleting all the "SUB TOTAL:" lines as well as all the blank lines...

Sub DataCleanUp()
Dim My_Rows As Long
Application.ScreenUpdating = False
For My_Rows = Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1
    If Range("A" & My_Rows).Value = "SUB TOTAL:" Or Range("A" & My_Rows).Value = "" Then _
        Range("A" & My_Rows).EntireRow.Delete
Next My_Rows
Application.ScreenUpdating = True
End Sub

I tried a pivot table but having to run the pivot table report weekly was time consuming.

It's not been my experience that running/refreshing a PivotTable report was ever time consuming. If you mean creating a PivotTable report is time consuming, you can turn on the Macro Recorder to capture your initial creation, then thereafter just run the recorded macro. You may need to tweak the range references to be dynamic rather than static so as to adapt to your changing data.

...my boss has given me 2 weeks to improve this process we have.

Most bosses would want it tomorrow!

Cheers,

tonyyy

 

[crazybuckeyeguy]

crazybuckeyeguy,

If all you want to do is to rename the "SUB TOTAL:" cell to the agents' name, then @onlyadrafter's code will do that. If, however, you want to run vlookup functions or create PivotTables against this, you might want to consider deleting all the "SUB TOTAL:" lines as well as all the blank lines...

Sub DataCleanUp()
Dim My_Rows As Long
Application.ScreenUpdating = False
For My_Rows = Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1
    If Range("A" & My_Rows).Value = "SUB TOTAL:" Or Range("A" & My_Rows).Value = "" Then _
        Range("A" & My_Rows).EntireRow.Delete
Next My_Rows
Application.ScreenUpdating = True
End Sub

It's not been my experience that running/refreshing a PivotTable report was ever time consuming. If you mean creating a PivotTable report is time consuming, you can turn on the Macro Recorder to capture your initial creation, then thereafter just run the recorded macro. You may need to tweak the range references to be dynamic rather than static so as to adapt to your changing data.



Most bosses would want it tomorrow!

Cheers,

tonyyy

LOL thx dude. they wanted it last week and I explained that I am not allowed to time travel anymore.