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.
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
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
 
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0
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"
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,495
Messages
6,113,992
Members
448,538
Latest member
alex78

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