Tags: COUNTA

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.

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

{0 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.

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.

Perform a Competitor, Feature and Customer Analysis with the PowerPivot

{2 Comments}

Assume there are four interrelated tables. One may want to create a pivot that allows one to filter data by using the slicers. Data should be filtered by the following interdependent slicers selections: Customer, Country and segment.  The logic behind the pivot when using the slicers shall be as follows:

1. Feature N is only shown if relevant to Customer X in Segment Y and Competitors do not possess Feature N
2. Competitor X is only shown if Competitor X exists and is active in Country Z and is relevant to Customer Y in Country Z

So after slicer selections are made, the idea is to display all features that one can offer and are relevant to the respective customer in the respective segment and country, regardless of whether the competitors can offer them or not.  So if one competes with competitor 1 in a specific project and offers features 1, 3, 7, offering the very same features to our shared customer does not make sense.  The customer won't see a benefit in choosing me over competitor 1.

Here's an elaborate example:

1. If one selects Customer 1/Segment A/Country 1 from the three slicers, then the Pivot Table should display as follows:

1. Row Labels - Display features in pivot if they are implemented or relevant.  The ones in Blue are implemented and the ones in green are relevant.  The pivot now shows that competitor 1 does not have features 2 and 9 which. Thus one's sales pitch will focus on offering features 2 and 9.  This section should also show data for Feature benefit calculation.

2. Column labels - Competitor 1 and 2 are displayed in the Pivot Table because they are both relevant in Country 1.

3. Competitor has/does not have feature (Value area section) - The following competitors have the same features I can offer my customer 1 in segment A:

  • Competitor 1 has features           1, 3, 7
  • Competitor 2 has features           3

Therefore they are marked with an “x” whenever they have the features.

The other competitors also have features that I offer to customer 1 in segment A, but since they are not active in Country 1, they are automatically not displayed when using the slicers.

You may refer to my solution in this workbook.

You may also view a video of my Power Query solution here:

Create a Pivot Table from multiple individual ranges without using ancillary columns

{16 Comments}

Assume that there are three separate tables showing the following information:

1. Date of visit data for visitors to a certain recreation facility.  The same visitor may visit the facility multiple times
2. Fee per visit in different cites
3. Region in which each city lies

The question is to analyse the three tables above via a Pivot Table to generate the following:

1. Region wise and visitor wise:
a.  Fee per visit
b.  Frequency of visit
c.  Revenue
2. Revenue collected by month wise and by visitor

In this workbook, I have shared two solutions:

1. PowerPivot solution - This solution answers both questions above without using any calculated/ancillary columns in the base data.  By establishing simple relationships in the PowerPivot window and by writing two calculated Field formulas, both questions above have been answered.  To use this PowerPivot solution, you need to be using the PowerPivot add-in for MS Excel.  This add-in is only available for Excel 2010 and higher versions.

2. Pivot Table solution - This solution answers both questions above by using calculated/ancillary columns in the base data.

Here's another example.  Assume a four column table showing Date of session, Client, Location and Participant Name.  Assume another four column table showing Client, Date of session, Date of invoice and Amount Billed.  The task is to determine the Amount billed per location.  You may refer to my PowerPivot and Pivot Table solution in this workbook.

Determine cumulative expenses per employee when per diem rates vary by block of dates

{8 Comments}

Assume per diem travel rates vary by block of dates (from and to).  So, assume the per diem rate for travel dates between 26/2/2013 and 28/2/2013 is Rs. 78,000/day.  Likewise, if a person travels between 1/3/2013 and 25/3/2013, the per diem rate applicable is Rs. 70,000/day.  With different travel dates (from and to) specified per traveller, the task is to determine total travel expenses per individual.

You may refer to my solution in this workbook.

LOOKUP unique data from multiple columns where search string appears multiple times

{2 Comments}

In a list with multiple occurrences, the VLOOKUP() function in Excel will only return the first occurrence of the string being searched.  Furthermore,  the VLOOKUP() function returns data from one column only.

To work with a list where there are multiple occurrences of a particular string and data has to be extracted from multiple columns, you may refer to my solution in this workbook.

To work with a list where there are multiple occurrences of a particular string and data has to be extracted from one column only, you may refer to my solution at this link.

Count unique values with conditions on large databases

{8 Comments}

Given a database of 50,000 rows, counting unique values with conditions using formulas would either adversely effect workbook performance or would not work in the first place at all.

In this workbook, I have shown the technique to count unique values with conditions on a large database

1. Using PowerPivot - Will only work in Excel 2010 and higher versions

2. Using a  normal Pivot Table and SUMPRODUCT() function - Will work for all versions but is not as efficient as the PowerPivot solution.

To count unique values with conditions on small databases, you may refer to the following link

Sum data from a particular cell of last n sheets only

{0 Comments}

A number is entered in cell A1 of every sheet in a multi sheet workbook.  The task is to sum numbers in cell C2 of the Summary sheet from cell A1 of last x sheets - x is the number of sheets (mentioned in cell B2 of the Summary sheet) from which numbers have to be summed up.  So if the number entered in cell B2 of sheet is 3, then sum numbers from cell A1 of last three sheets.  Worksheets can get added, removed or renamed.

You may refer to my solution in this workbook.