Summarise cells - row by row

leeksleeks

Board Regular
Joined
Oct 31, 2013
Messages
95
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>
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,049
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:

leeksleeks

Board Regular
Joined
Oct 31, 2013
Messages
95
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?
 

leeksleeks

Board Regular
Joined
Oct 31, 2013
Messages
95
Microsoft Office Home and Business 2016
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,049
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 ;)

 
Last edited:

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,049
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:

 
Last edited:

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,424
Office Version
365
Platform
Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,095,669
Messages
5,445,872
Members
405,366
Latest member
Not_Really_Me

This Week's Hot Topics

Top