Summarise cells - row by row

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:

08:00name.1SALES 2
08:00name.4TRAINING
08:00name.6SALES 1
08:30name.1SALES 2

<tbody>
</tbody>

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.

name.1name.2name.3name.4name.5name.6name.7name.8
08:00SALES 2 TRAINING SALES 1
08:30SALES 2 TRAINING SALES 1
09:00SALES 2 TRAINING SALES 1 TRAINING
09:30SALES 2 TRAINING SALES 1 TRAINING
10:00SALES 2 SALES 1TRAINING SALES 4SALES 5SALES 6
10:30SALES 2 SALES 1TRAINING SALES 4SALES 5SALES 6
11:00SALES 2 SALES 1TRAINING SALES 4SALES 5TRAINING
11:30SALES 2 SALES 1TRAINING SALES 4SALES 5TRAINING
12:00SALES 2 SALES 1TRAINING SALES 4SALES 4TRAINING
12:30SALES 2 SALES 1TRAINING OOFSALES 4TRAINING
13:00SALES 2SALES 2SALES 1TRAININGSALES 1OOFSALES 4TRAINING
13:30SALES 4SALES 2SALES 1TRAININGSALES 1OOFSALES 4TRAINING
14:00SALES 4MEETINGSALES 1TRAININGMEETINGSALES 2SALES 2TRAINING
14:30SALES 4MEETINGSALES 1TRAININGMEETINGSALES 2SALES 2TRAINING
15:00SALES 4MEETINGSALES 1TRAININGMEETINGSALES 2SALES 2TRAINING
15:30SALES 4MEETINGSALES 1TRAININGMEETINGSALES 2SALES 2TRAINING
16:00SALES 4MEETINGSALES 1TRAININGMEETINGSALES 2SALES 2TRAINING
16:30 SALES 4SALES 1 TRAINING SALES 2TRAINING
17:00 SALES 4SALES 1 TRAINING SALES 2TRAINING
17:30 SALES 4SALES 1 TRAINING SALES 2
18:00 SALES 4SALES 1 TRAINING SALES 2
18:30 SALES 2 SALES 1
19:00 SALES 2 SALES 1
19:30 SALES 2 SALES 1
20:00 SALES 2 SALES 1
20:30 SALES 2 SALES 1
21:00 SALES 2 SALES 1
21:30

<colgroup><col span="3"><col><col span="2"><col><col><col></colgroup><tbody>
</tbody>

<tbody>
</tbody>
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
something like this?

TimeNameAction
08:00:00​
name.1SALES 2
08:00:00​
name.4TRAINING
08:00:00​
name.6SALES 1
08:30:00​
name.1SALES 2
08:30:00​
name.4TRAINING
08:30:00​
name.6SALES 1
09:00:00​
name.1SALES 2
09:00:00​
name.4TRAINING
09:00:00​
name.6SALES 1
09:00:00​
name.8TRAINING
09:30:00​
name.1SALES 2
09:30:00​
name.4TRAINING
09:30:00​
name.6SALES 1
09:30:00​
name.8TRAINING
10:00:00​
name.1SALES 2
10:00:00​
name.3SALES 1
10:00:00​
name.4TRAINING
10:00:00​
name.6SALES 4
10:00:00​
name.7SALES 5
10:00:00​
name.8SALES 6
10:30:00​
name.1SALES 2
10:30:00​
name.3SALES 1
10:30:00​
name.4TRAINING
10:30:00​
name.6SALES 4
10:30:00​
name.7SALES 5
10:30:00​
name.8SALES 6
11:00:00​
name.1SALES 2
11:00:00​
name.3SALES 1
11:00:00​
name.4TRAINING
11:00:00​
name.6SALES 4
11:00:00​
name.7SALES 5
11:00:00​
name.8TRAINING
11:30:00​
name.1SALES 2
11:30:00​
name.3SALES 1
11:30:00​
name.4TRAINING
11:30:00​
name.6SALES 4
11:30:00​
name.7SALES 5
11:30:00​
name.8TRAINING
12:00:00​
name.1SALES 2
12:00:00​
name.3SALES 1
12:00:00​
name.4TRAINING
12:00:00​
name.6SALES 4
12:00:00​
name.7SALES 4
12:00:00​
name.8TRAINING
12:30:00​
name.1SALES 2
12:30:00​
name.3SALES 1
12:30:00​
name.4TRAINING
12:30:00​
name.6OOF
12:30:00​
name.7SALES 4
12:30:00​
name.8TRAINING
13:00:00​
name.1SALES 2
13:00:00​
name.2SALES 2
13:00:00​
name.3SALES 1
13:00:00​
name.4TRAINING
13:00:00​
name.5SALES 1
13:00:00​
name.6OOF
13:00:00​
name.7SALES 4
13:00:00​
name.8TRAINING
13:30:00​
name.1SALES 4
13:30:00​
name.2SALES 2
13:30:00​
name.3SALES 1
13:30:00​
name.4TRAINING
13:30:00​
name.5SALES 1
13:30:00​
name.6OOF
13:30:00​
name.7SALES 4
13:30:00​
name.8TRAINING
14:00:00​
name.1SALES 4
14:00:00​
name.2MEETING
14:00:00​
name.3SALES 1
14:00:00​
name.4TRAINING
14:00:00​
name.5MEETING
14:00:00​
name.6SALES 2
14:00:00​
name.7SALES 2
14:00:00​
name.8TRAINING
14:30:00​
name.1SALES 4
14:30:00​
name.2MEETING
14:30:00​
name.3SALES 1
14:30:00​
name.4TRAINING
14:30:00​
name.5MEETING
14:30:00​
name.6SALES 2
14:30:00​
name.7SALES 2
14:30:00​
name.8TRAINING
15:00:00​
name.1SALES 4
15:00:00​
name.2MEETING
15:00:00​
name.3SALES 1
15:00:00​
name.4TRAINING
15:00:00​
name.5MEETING
15:00:00​
name.6SALES 2
15:00:00​
name.7SALES 2
15:00:00​
name.8TRAINING
15:30:00​
name.1SALES 4
15:30:00​
name.2MEETING
15:30:00​
name.3SALES 1
15:30:00​
name.4TRAINING
15:30:00​
name.5MEETING
15:30:00​
name.6SALES 2
15:30:00​
name.7SALES 2
15:30:00​
name.8TRAINING
16:00:00​
name.1SALES 4
16:00:00​
name.2MEETING
16:00:00​
name.3SALES 1
16:00:00​
name.4TRAINING
16:00:00​
name.5MEETING
16:00:00​
name.6SALES 2
16:00:00​
name.7SALES 2
16:00:00​
name.8TRAINING
16:30:00​
name.2SALES 4
16:30:00​
name.3SALES 1
16:30:00​
name.5TRAINING
16:30:00​
name.7SALES 2
16:30:00​
name.8TRAINING
17:00:00​
name.2SALES 4
17:00:00​
name.3SALES 1
17:00:00​
name.5TRAINING
17:00:00​
name.7SALES 2
17:00:00​
name.8TRAINING
17:30:00​
name.2SALES 4
17:30:00​
name.3SALES 1
17:30:00​
name.5TRAINING
17:30:00​
name.7SALES 2
18:00:00​
name.2SALES 4
18:00:00​
name.3SALES 1
18:00:00​
name.5TRAINING
18:00:00​
name.7SALES 2
18:30:00​
name.2SALES 2
18:30:00​
name.5SALES 1
19:00:00​
name.2SALES 2
19:00:00​
name.5SALES 1
19:30:00​
name.2SALES 2
19:30:00​
name.5SALES 1
20:00:00​
name.2SALES 2
20:00:00​
name.5SALES 1
20:30:00​
name.2SALES 2
20:30:00​
name.5SALES 1
21:00:00​
name.2SALES 2
21:00:00​
name.5SALES 1

if it is acceptable here is M-code for PowerQuery

Code:
[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"Time", type time}, {"name.1", type text}, {"name.2", type text}, {"name.3", type text}, {"name.4", type text}, {"name.5", type text}, {"name.6", type text}, {"name.7", type text}, {"name.8", type text}}),
    Unpivot = Table.UnpivotOtherColumns(Type, {"Time"}, "Attribute", "Value"),
    Sort = Table.Sort(Unpivot,{{"Time", Order.Ascending}, {"Attribute", Order.Ascending}, {"Value", Order.Ascending}}),
    Rename = Table.RenameColumns(Sort,{{"Attribute", "Name"}, {"Value", "Action"}})
in
    Rename[/SIZE]
 
Last edited:
Upvote 0
Hi sandy666. This is exactly what I am looking for. Excuse my excel inferiority but where do I put this query in excel to return this result?
 
Upvote 0
Ops! I think you will need to wait for someone else with VBA or any formula solution because afaik MSO H&B doesn't support PowerQuery (or I am wrong?)

edit:
but you can check: Data tab and if you see Show Queries - it means you can use PowerQuery ;)

screenshot-42.png
 
Last edited:
Upvote 0
add header for blank header column eg. Time
and try Alt+D+P

add whole range
put each field to the ROWS area
choose tabular form and repeat all item labels
turn off subtotals and grand totals
sort by time
filter by blank

eg. like this:

screenshot-43.png
 
Last edited:
Upvote 0
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.
Try this in a copy of your workbook. It assumes original data has names in row 1 and times in column A.
The new table is placed to the right of the original table.
Code:
Sub Make_Table()
  Dim aData As Variant, aResults As Variant
  Dim rws As Long, cols As Long, i As Long, j As Long, k As Long
  
  cols = Cells(1, Columns.Count).End(xlToLeft).Column
  With Range("A1", Range("A" & Rows.Count).End(xlUp)).Resize(, cols)
    aData = .Value
    rws = UBound(aData)
    ReDim aResults(1 To rws * (cols - 1), 1 To 3)
    For i = 2 To rws
      For j = 2 To cols
        If Len(aData(i, j)) > 0 Then
          k = k + 1
          aResults(k, 1) = aData(i, 1)
          aResults(k, 2) = aData(1, j)
          aResults(k, 3) = aData(i, j)
        End If
      Next j
    Next i
    If k > 0 Then
      With .Offset(, cols + 1).Resize(, 3)
        .Resize(1).Value = Array("Time", "Name", "Task")
        With .Offset(1).Resize(k)
          .Columns(1).NumberFormat = "hh:mm"
          .Value = aResults
        End With
        Columns.AutoFit
      End With
    End If
  End With
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,205
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top