Complex pivot table with dates

esamk

New Member
Joined
Jun 26, 2020
Messages
9
Office Version
  1. 2013
Platform
  1. Windows
Morning,

I have a calendar type sheet to track people, and would like to know if it's possible to create a summary by date using a pivot table. Unfortunately I can't copy/paste from my computer or import VBA (it's an offline system) so I need the solution in writing - if there even is one. Thank you in advance.

The source data is a complicated sheet with multiple headers etc. Essentially you can pick one of several set activities (fixed by data validation) under the corresponding person (row) and date (column). The days are also formatted as dates. Filtering by title, dept etc is desired also. Count of blanks would help also.

e.g:

3-Aug-204-Aug-205-Aug-20
SurnameInitialsTitleDept.MonTuesWed
SMITHA.B.MrHRLeaveLeave
SIMMONSI.J.MrHR
JOHNC.D.MrsPRODTrainingTraining
HOPKINSG.G.MrPRODTraining
JONESE.F.MrITConference
HENRYG.H.MrIT

Would show in a pivot table with the 4th and Dept. filters selected as:

HRPRODITGrand total
Leave11
Training22
Conference11
(Blank)112
Sub Total2226

If there is no pivot table solution, I will just make a formula based fixed table.
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

GraH

Well-known Member
Joined
Mar 22, 2020
Messages
702
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
In Office 2013 you also have Power Query on board. You can transform your data (unpivot) and save the query as a connection only which you use in the pivot as external data source.

The query would basically work like this, notice I've name the range of data to prevent it to be changed into a table with headers. This script is made by using the GUI and some mouse actions.
Power Query:
let
    Source = Excel.CurrentWorkbook(),
    rData = Source{[Name="rData"]}[Content],
    Transpose = Table.Transpose(rData),
    MergeCols1_2 = Table.CombineColumns(Table.TransformColumnTypes(Transpose, {{"Column1", type text}}, "nl-BE"),{"Column1", "Column2"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"Headers"),
    reTranspose = Table.Transpose(MergeCols1_2),
    PromoteHeaders = Table.PromoteHeaders(reTranspose, [PromoteAllScalars=true]),
    ReplaceBlanksByNone = Table.ReplaceValue(PromoteHeaders,null,"None",Replacer.ReplaceValue,{"|Surname", "|Initials", "|Title", "|Dept.", "3/08/2020 0:00:00|Mon", "4/08/2020 0:00:00|Tues", "5/08/2020 0:00:00|Wed"}),
    Unpivot = Table.UnpivotOtherColumns(ReplaceBlanksByNone, {"|Surname", "|Initials", "|Title", "|Dept."}, "Attribute", "Value"),
    SplitColByPipe = Table.SplitColumn(Unpivot, "Attribute", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
    SetColTypes = Table.TransformColumnTypes(SplitColByPipe,{{"Attribute.1", type datetime}, {"Attribute.2", type text}}),
    RenameCols = Table.RenameColumns(SetColTypes,{{"Attribute.1", "Date"}, {"Attribute.2", "Day"}, {"|Surname", "Surname"}, {"|Initials", "Initials"}, {"|Title", "Title"}, {"|Dept.", "Dept."}})
in
    RenameCols

1598710399799.png
 

esamk

New Member
Joined
Jun 26, 2020
Messages
9
Office Version
  1. 2013
Platform
  1. Windows
Not having much luck, so I am building a fixed table - is there a way to use COUNTIFS or something similar to cross reference TODAY() and "Title" column to spit out a total for each activity?
 

esamk

New Member
Joined
Jun 26, 2020
Messages
9
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

OK, gave it a shot but not sure where I'm going wrong.

e.g: total of people from HR at training or conference:

=SUM(COUNTIFS(MATCH(TODAY(),1:1,0),{"TRAINING","CONFERENCE"},$D1:$D100,"HR"))

I can get a return using a specific column ref, but having difficulty getting it to convert that to a TODAY() match. Any suggestions?
 

esamk

New Member
Joined
Jun 26, 2020
Messages
9
Office Version
  1. 2013
Platform
  1. Windows
Power Query:
PromoteHeaders = Table.PromoteHeaders(reTranspose, [PromoteAllScalars=true]),

I'm getting an error on the highlighted line; "Expression.Error: We cannot convert the value 1 to type Text"
 

GraH

Well-known Member
Joined
Mar 22, 2020
Messages
702
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

That is a typical error for charge type Steps. I doubt it is the promote header step.
Is there a change type step before?

Just noticed I missed your previous reaction. Not intentionally, just being busy with work. When I have a bit more time I could ook at it again.
 

esamk

New Member
Joined
Jun 26, 2020
Messages
9
Office Version
  1. 2013
Platform
  1. Windows
Just noticed I missed your previous reaction. Not intentionally, just being busy with work. When I have a bit more time I could ook at it again.

No worries, I'm grateful for the help. There's a reTranspose and a mergeCols1_2 ahead of it.
 

GraH

Well-known Member
Joined
Mar 22, 2020
Messages
702
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi again,

I think the error comes from this step actually
Power Query:
MergeCols1_2 = Table.CombineColumns(Table.TransformColumnTypes(Transpose, {{"Column1", type text}}, "nl-BE"),{"Column1", "Column2"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"Headers"),
Can you check the column types in your real life workbook. And define the columns to merge as text, even when they do contain numbers.

1599247870615.png
= Any
1599247898479.png
= Text
You change like below
1599247941290.png
 

Watch MrExcel Video

Forum statistics

Threads
1,122,806
Messages
5,598,188
Members
414,218
Latest member
speedbit

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
Top