In a Pivot Table, show the most frequently appearing text entry by a certain parameter

{0 Comments}

Here's a simple two column dataset

Comment Identifier Intervals
A 3pm-6pm
A 9pm-12pm
S 3pm-6pm
S 3pm-6pm
S 9pm-12pm
A 9pm-12pm
S 9pm-12pm
D 3pm-6pm
A 9pm-12pm
A 9pm-12pm
A 9pm-12pm
A 3pm-6pm
A 3pm-6pm

For identifiers listed in column A, there are time intervals in column B. Note that for a certain identifier, a time interval can appear multiple times. The objective is two-fold:

  1. For each identifier, show the time interval which appears most frequently; and
  2. For each identifier, compute the count of the time interval which appears most frequently

The expected result is:

untitled

As you can observe, the result for S is two time periods - 3pm-6pm and 9pm-12pm.  This is because each of them appears twice.

You may download my solution from here.

Filter a column of a Pivot Table on a certain condition but also show other items from that column

{2 Comments}

The title sounds confusing!!!!.  Please bear with me and read on.  Here's a simple dataset

Client ID Client Name Resource Project ID Billable amount
1 Alpha David 1000 10
1 Alpha Henry 1001 20
1 Alpha Rakesh 1002 30
1 Alpha Alice 1003 40
2 Beta Alice 1000 50
2 Beta Alicia 1002 60
2 Beta Patrick 1003 70
2 Beta Mukesh 1004 80
2 Beta Suresh 1006 90
2 Beta Ajay 1005 100
3 Gamma Rama 1004 110
3 Gamma Sakshi 1006 120
4 Theta Prabhu 1005 130
5 Epsilon Alice 1000 140
5 Epsilon Alicia 1001 150
5 Epsilon Prabhu 1002 160
5 Epsilon Sakshi 1003 170
5 Epsilon Raghav 1008 180
5 Epsilon David 1010 190
5 Epsilon Henry 1012 200

Here's a Pivot Table built from the dataset above.

untitled

The question is "Is there a way to show only those rows of data which have Alice but also show others who worked with Alice".  While the first part of the question can be answered easily by filtering the Resource column on Alice, the second part (italicized for your reference) of the question is the real challenge.  When one filters the Resource column on Alice, the result is as seen below:

untitled1

This view does not show me who else worked with Alice.  The result I am expecting to see is:

untitled2

This problem can be resolved with the help of the Query Editor (Power Query).  The basic idea is to create another column in the original dataset where we create a string of all resources for every row.  So for example, in every row of Client ID1, the sixth column should show David,Henry,Rakesh.Alice and so on.  Once this is done, one can simply take this column to the Report filter section of the Pivot Table (see last image above) and filter on Alice.

You may refer to my solution in this workbook.

Show months with no data which fall within a certain date range of a Pivot Table

{4 Comments}

Here's a simple 4 column dataset

Bacterin Donor# Recovery Agency Date Donor Received DONOR STATUS
B050001 1 09-06-2005 00:00 ACCEPT
B050002 3 09-06-2005 00:00 ACCEPT
B050003 1 09-06-2005 00:00 ACCEPT
B050004 1 09-06-2005 00:00 ACCEPT
B050005 1 09-06-2005 00:00 ACCEPT
B050006 1 09-06-2005 00:00 ACCEPT
B050007 1 09-06-2005 00:00 ACCEPT
B050008 4 09-06-2005 00:00 ACCEPT

The objective is to determine "The number of Bacterin Donor #'s which fall within January 1, 2016 and September 30, 2017 for Agency number 18 split by Donor Status". To answer this question. one can build a Pivot Table by dragging Recovery agency and Date to the Row labels, Donor Status to the column labels and Bacteria Donor # to the value area section. One can then group the dates by years and months and apply a criteria of 18 on the Recovery agency.  One can then apply a Between filter on dates.

untitled

As you can see in the image above August 2016 and May 2017 are missing because there is no data for that period. However one would like to show those 2 months as well. To do so, one can go to Pivot Table Options > Display and click on "Show items with no data on rows". However, as you can see below, that box is greyed out.

untitled1

The only other option left is to go to Field Settings > Layout & Print and check the box for "Show items with no data"

untitled3

On doing so, the problem is that all dates across all months and years show in the Pivot Table despite the Between Date filter staying intact:

untitled4

So the question is, how can one show even those months (within a chosen date range) in which there is no data.  In other words, one has to think of a way to activate the check box of "Show items with no data on rows" available under Pivot Table Options > Display.

untitled5

I have solved this problem with the help of the PowerPivot.  You may download my solution workbook from here.

Distribute projected revenue annually

{2 Comments}

Here is a dataset showing Project wise forecast of open opportunities.

  1. Topic is the Project Name
  2. Est. Close Date is the date by when the opportunity would be closed i.e. the project would be won from that Client
  3. Duration is the time (in months) for which the project would run
  4. Amount is the total amount that would be billed for that project

Clients are invoiced annually only. So in the example below:

  1. Project ABC is for US$1 million with a duration of 24 months and is expected to be closed in Oct. 2017.  We need to model the data to show the billing every 12 months.  So for ABC US$500K would be billed in Oct-2017 and another US$500K in Oct-2018.
  2. Project GEF is for US$2 million with a duration of 18 months and is expected to be closed in Feb. 2018. We need to model the data to show US$1.3 million in Feb-2018 and another US$666K in Feb-2019.  The monthly billing is US$2 million divided by 18 and then multiplied by 12 - this amounts to US$1.3 million.
Topic Est. Close Date Duration (Months) Amount
ABC 01-10-2017 24 1,000,000
GEF 01-02-2018 18 2,000,000
XYZ 01-03-2018 30 1,000,000

The expected result should look like this:

Row Labels Oct-17 Feb-18 Mar-18 Oct-18 Feb-19 Mar-19 Mar-20 Total
ABC 500,000 500,000 1,000,000
GEF 1,333,333 666,667 2,000,000
XYZ 400,000 400,000 200,000 1,000,000
Grand Total 500,000 1,333,333 400,000 500,000 666,667 400,000 200,000 4,000,000

I have solved this problem using Power Query and PowerPivot. You may download my solution workbook from here.

Alter the behaviour of a filter/slicer from OR to AND

{3 Comments}

Here is a simple two column dataset which shows EmpID in column A and DateWorked in column B.  My objective is to filter the table to show those employees who worked ONLY on August 17 and August 18.  I'd like to exclude employees who:

  1. Did not work on both these days; and
  2. Worked on both these dates but also worked on other dates

A simple filter on the DatesWorked column with the criteria of August 17 and August 18, would return 7 employees.  This is clearly wrong because this will show employees who:

  1. Worked on any of the two chosen dates; and
  2. Worked on dates other than the two chosen dates

From Table 1, one can clearly observe that the correct result should be EmpID E.  Only EmpID E worked on August 17 and 18.  While others also worked on both these dates, they worked on other dates as well and should therefore be excluded.

The problem outlined above holds true for selections made via a slicer in a Pivot Table as well.  So this is a generic problem with a filter/slicer - conditions specified therein behave like OR conditions, not like AND conditions.

Base dataset

EmpID DateWorked
A 14-08-2017
A 15-08-2017
A 16-08-2017
A 17-08-2017
A 18-08-2017
B 15-08-2017
B 16-08-2017
B 18-08-2017
C 14-08-2017
C 15-08-2017
C 16-08-2017
C 17-08-2017
C 18-08-2017
D 14-08-2017
D 15-08-2017
D 16-08-2017
D 17-08-2017
D 18-08-2017
E 17-08-2017
E 18-08-2017
F 16-08-2017
F 17-08-2017
G 18-08-2017

Filtered dataset (after specifying the criteria as 17 Aug and 18 Aug)

EmpID DateWorked
A 17-08-2017
A 18-08-2017
B 18-08-2017
C 17-08-2017
C 18-08-2017
D 17-08-2017
D 18-08-2017
E 17-08-2017
E 18-08-2017
F 17-08-2017
G 18-08-2017

So the question is "How does one change the behavior of a filter/slicer from OR to AND?". To make things worse, the data is arranged as follows:

EmpID DateWorked
A 14/8/17;15/8/17;16/8/17;17/8/17;18/8/17
B 15/8/17;16/8/17;18/8/17
C 14/8/17;15/8/17;16/8/17;17/8/17;18/8/17
D 14/8/17;15/8/17;16/8/17;17/8/17;18/8/17
E 17/8/17;18/8/17
F 16/8/17;17/8/17
G 18-08-17

As you can see, there are multiple dates in a single cell separated by a ;.

You may refer to my Power Query and PowerPivot solution here.

Return the specific product which satisfies the user defined feature combination

{2 Comments}

Here is a simple matrix like data layout which shows the features available in every product.  Let's assume that this data is in range A2:E8 (including the header row)

Functions Product A Product B Product C Product D
Function 1 x
Function 2 x x
Function 3 x x x
Function 4 x x
Function 5 x x
Function 6 x

Here is a user created input table of his/her requirements.  Let's assume that this data is in range A12:B18 (including the header row)

Functions Input
Function 1
Function 2 x
Function 3 x
Function 4
Function 5 x
Function 6

The expected result is the "Product" which meets the user defined function combinations. The result should be Product B. If there are different products which meet the user's requirements, only the first one will be returned as a result. If one would like all products to be returned, the one can use Power Query to resolve this problem.

=IFERROR(INDEX($B$2:$E$2,1,MATCH(COUNTA($A$13:$A$18),MMULT(1*(TRANSPOSE(B3:E8=B13:B18)),1*(ISNUMBER(ROW(INDIRECT("1:"&COUNTA($A$3:$A$8)))))),0)),"No such product")

Please note that this is an array formula so please confirm the formula with Ctrl+Shift+Enter.

Fill out a matrix with a user defined value which has variable start and end points

{0 Comments}

Here is a sample dataset with Item and Number of buckets in the row labels and Year-Month in the column labels.  In the value area section are some numbers.

Item Number of Buckets 2016-10 2016-11 2016-12 2017-1 2017-2 2017-3 2017-4 2017-5
ABC 8 1500
PQR 12 40
RPS 4 100
CHA 11 30
MUM 12 90
CHE 2 24

The objective is to repeat the number in every row of the value area section as many times as the number mentioned in the Number of buckets column.  As a case in point, for RPS, 100 should appear 3 more times in that row (till 2017-7).  The result should look like this

Item Number of Buckets 2016-10 2016-11 2016-12 2017-1 2017-2 2017-3 2017-4 2017-5
ABC 8 1500
PQR 12 40 40 40 40 40
RPS 4 100 100
CHA 11 30 30 30 30
MUM 12 90 90 90 90 90 90 90 90
CHE 2 24

For want of space I have deleted the columns from the right.

I have solved this problem using Power Query a.k.a. Get & Transform in Excel 2016 (available under Data).  You may download my workbook from here.

Merge data from 2 data sources in a Pivot Table to get a Consolidated Project view

{0 Comments}

Here's a simple four column table showing date wise amount spent per project

Date Project ID Project Name Total Amount
10-01-2015 P250 Project A 100
15-03-2015 P250 Project A 250
01-08-2015 P250 Project A 175
01-12-2015 P250 Project A 90
16-01-2016 P250 Project A 75
28-02-2016 P250 Project A 105
23-02-2016 P300 Project B 175
01-03-2016 P300 Project B 85
06-03-2016 P300 Project B 66
07-04-2016 P300 Project B 150
15-03-2016 P300 Project B 25
28-03-2016 P300 Project B 200
26-04-2016 P300 Project B 450

Here's another table which shows project wise gates assigned within a time frame.

Project ID Project Name Project Gates Beginning date Ending date
P250 Project A Release Gate 01-01-2015 31-05-2015
P250 Project A Mid Gate 01-06-2015 30-12-2015
P250 Project A Review Gate 31-12-2015 31-01-2016
P250 Project A Final Gate 01-02-2016 29-02-2016
P300 Project B Release Gate 15-02-2016 29-02-2016
P300 Project B Mid Gate 01-03-2016 14-03-2016
P300 Project B Review Gate 15-03-2016 14-04-2016
P300 Project B Final Gate 15-04-2016 31-12-2016

The result expected is a Pivot Table which should mesh data from both datasets:

Project ID Project Name Date Gates Project Amount
P250 Project A 10-01-2015 Release Gate 100.00
15-03-2015 Release Gate 250.00
01-08-2015 Mid Gate 175.00
01-12-2015 Mid Gate 90.00
16-01-2016 Review Gate 75.00
28-02-2016 Final Gate 105.00
P300 Project B 23-02-2016 Release Gate 175.00
01-03-2016 Mid Gate 85.00
06-03-2016 Mid Gate 66.00
15-03-2016 Review Gate 25.00
28-03-2016 Review Gate 200.00
07-04-2016 Review Gate 150.00
26-04-2016 Final Gate 450.00
Grand Total       1,946.00

While the 1st, 2nd, 3rd and 5th columns are easy to get in a Pivot Table from Table 1, the challenge is to get the "Gates" information from Table 2 inside the Pivot Table.

You may refer to my Power Query a.k.a Get & Transform (available under Data) in Excel 2016 and PowerPivot solution here.

Compute standard hours spent on weekdays by Tier, Week, Month and Country

{0 Comments}

Imagine a Sales dataset with the following columns - ID, Country, Start date, End date and Tier. Here's a snapshot of the table:

ID Country Start date End date Tier
33948 ES 25-Sep-17 28-Sep-17 3
19820 US 08-Oct-17 17-Oct-17 4
9118 US 27-Oct-17 03-Nov-17 2
1563 ES 02-Sep-17 07-Sep-17 2
11087 US 18-Oct-17 27-Oct-17 1
15057 US 05-Nov-17 13-Nov-17 4
13567 ES 18-Oct-17 26-Oct-17 4
22362 DE 28-Aug-17 30-Aug-17 4
31301 FR 04-Nov-17 10-Nov-17 2
22838 US 05-Sep-17 13-Sep-17 4

There is another 2 column table which lists down the Tier wise standard hours. Here's a snapshot of the table:

Tier Daily load hh
1 0.7
2 0.72
3 0.8
4 1.2

The result which one expects from these two tables is shown below:

Tier
Month Week 1 2 3 4 Grand total   Slicer
9 35 x x x x x US
36 x x x x x UK
37 x x x x x FR
38 x x x x x IT
39 x x x x x ES
Grand total   x x x x x    

Let's take a specific example to show the result expected for the US Region. From the last row of the Sales Table, one can see that there is a ID 22838 in US which spans the date range 05-Sept-17 to 13-Sep-17. These dates span week numbers 36 (from 05-Sept-17 to 08-Sep-17) and 37 (from 11-Sept-17 to 13-Sep-17) [The week numbers have been obtained by using Excel's WEEKNUM() function]. So, is US, for Tier 4, in Week 36, the standard hours worked on weekdays would be 4.8 (4 days * 1.2 hours per day). Likewise, in week 37, the standard hours worked on weekdays would be 3.6 (3 days * 1.2 hours).

I have solved this problem using Power Query a.k.a Get & Transform (Available under Data) in Excel 2016 and PowerPivot.  You may download my solution workbook from here.

Determine cumulative interest payable on an annuity with varying time periods

{0 Comments}

Imagine a fixed monthly amount due to an Organisation for services rendered to various customers.  While an invoice is raised every month by this Organisation, not all pay up the dues on time.  For unpaid dues, the Organisation charges its client interest ranging from 3% to 9% per annum.  The objective is to determine cumulative interest payable by various customers to Organisation X.

The base data looks like this

Client Monthly revenue Int. calculation start date Int. calculation end date Interest rate
Client A 33,967 01-Aug-16 25-Jul-17 9.00%
Client B 123 12-Sep-16 30-Nov-17 4.00%

Given the dataset above, the total interest payable by Client A is Rs. 16,237.20.  The calculation is shown below:

From To Days for which interest should be paid Principal Interest
02-Aug-16 31-Aug-16 328.00 33,967.00 2,745.26
01-Sep-16 30-Sep-16 298.00 33,967.00 2,494.17
01-Oct-16 31-Oct-16 267.00 33,967.00 2,234.71
01-Nov-16 30-Nov-16 237.00 33,967.00 1,983.62
01-Dec-16 31-Dec-16 206.00 33,967.00 1,724.16
01-Jan-17 31-Jan-17 175.00 33,967.00 1,464.70
01-Feb-17 28-Feb-17 147.00 33,967.00 1,230.34
01-Mar-17 31-Mar-17 116.00 33,967.00 970.88
01-Apr-17 30-Apr-17 86.00 33,967.00 719.79
01-May-17 31-May-17 55.00 33,967.00 460.33
01-Jun-17 30-Jun-17 25.00 33,967.00 209.24
01-Jul-17 25-Jul-17 - 33,967.00 -
Total       16,237.20

You may download my solution workbook with from here. I have solved this problem using normal Excel formulas and the PowerPivot.