Determine the total number of projects by Status

{ 0 Comments }

Here’s a simple 3 column table showing Date, Project name (Cat.) and Status of the project.  Each project can have multiple status entries on different dates.  So as you can observe, project “alpha_9383993” was In Progress on Oct 2, 2017, remained so on October 5, 2017 but was completed on October 6, 2017. Date Cat. […]

Read More →

In a Pivot Table, compute highest revenue earned on any day from each customer and the date thereof

{ 0 Comments }

Here’s a simple dataset showing the Date of sale, Customer Name and Sales amount. Date Customer Name Sales amount 12-03-2017 A 1 12-03-2017 A 2 12-03-2017 A 3 12-03-2017 B 4 12-03-2017 B 5 12-03-2017 B 6 12-03-2017 B 7 12-03-2017 B 8 13-03-2017 A 1 13-03-2017 A 1 The objective is to determine Customer […]

Read More →

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 […]

Read More →

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 […]

Read More →

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 […]

Read More →

Distribute projected revenue annually

{ 9 Comments }

Here is a dataset showing Project wise forecast of open opportunities. Topic is the Project Name Est. Close Date is the date by when the opportunity would be closed i.e. the project would be won from that Client Duration is the time (in months) for which the project would run Amount is the total amount […]

Read More →

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: Did not work on both these days; and Worked on both […]

Read More →

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 […]

Read More →

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 […]

Read More →

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 […]

Read More →