Drop additional fields in the Row area of a Pivot Table without affecting the already computed “conditional maximum” in the Value Area section

{2 Comments}

When creating a Pivot Table one can easily apply the Maximum function for figures in the Value area section by right clicking and choosing Maximum from "Summarise Values By" > Maximum.  The maximum so computed for figures in the Value area section is a "conditional maximum" because the numbers in the Value area section will be affected by whatever fields are dropped into/dragged out of the Row label, column label and Report Filter sections.

However, think of a case where you have already dragged 2 Fields in the Report Filter section, one in the Row label section and one numeric field in the Value area section (the summarization function used for figures in the Value area section is "Maximum").  The requirement now is to drag another field in the Row label section without affecting the figures (which are summarized by the Maximum function under Summarise Value By) appearing in the Value area section.

You may refer to the actual dataset, expected solution and my solution in this workbook.

I have used Power Query (a free add-in by Microsoft for MS Excel 2010 and higher versions) to resolve this problem.

Perform different calculations in the Subtotal/Grand Total column of a Pivot Table

{0 Comments}

Visualise a Pivot Table with a few Fields dragged in the Report filter, Row labels and Value Area section.  In the Column labels are two fields, Month and then Year - so in the column labels, for every month, there is data for three years 2005, 2006 and 2007.  For some months, there is data for two years only 2005 and 2006.  In the Value area section are fields such as Net Amount, Quantity, Bonus etc. and the summarization function applied to them is SUM.  There is no complication in creating the Pivot Table described above.

The actual requirement is to customise the Subtotal column of the Pivot Table as follows:

1. For the monthly subtotals, the Net Amount and Bonus figure are to computed as a difference of 2005 and 2006 i.e. SUM of quantity of 2005 - SUM of quantity of 2006.  The Grand total column should be a a summation of individual subtotals.
2. Average Selling price for every year is to be computed as as Net Amount/(Ttl Bonus + Quantity).  For the monthly subtotals, the figure is to be computed as

=(Net Amount of 2005/((Bonus of 2005+Quantity of 2005)) - (Net Amount of 2006/((Bonus of 2006+Quantity of 2006))

The Grand Total column is to be left blank for Average Selling Price,

As you can observe, the subtotal column (for the months) will have different formulas running for different Fields.

A conventional Pivot Table does not allow one to have custom formulas in the Subtotal columns.  I have been able to resolve this problem by using the free Power Pivot add-in from Microsoft for Excel 2010 and higher versions.

You may refer to my solution in this workbook.

Compute year wise weighted average on a large dataset

{0 Comments}

Assume a dataset with a Key Performance Indicator (KPI) [appearing in one column] data for years ranging from 1985 to 2010 for 114 countries.  This dataset has 170,000 rows of data and one row below the last row for every country, there is a total of the KPI column.  So, if there are 25 rows for India, then in the 26th row, there will be the total appearing for numbers in the KPI column.  The same is occurring for other countries as well.

There is another dataset (in another worksheet of the same workbook) which has an index value for the same countries and same date range as the first dataset.  The second dataset is relatively smaller (with only 1315 rows) because the index value is not available for all years of each country.

The objective is to determine the year wise (for all years from 1985 to 2010) weighted average of KPI.  An illustration of the weighted average computation has been shown in range F5:H10 of the "Index" worksheet of the workbook link shared below.

Solving this problem using Pivot Table, filters, formulas will slow down processing speed due to sheer size of data.  I have solved this problem using the Power Pivot tool (for Excel 2010 and higher versions).

You may refer to my solution in this workbook.

Remove abbreviations appearing before a name

{0 Comments}

Assume there is a list of names appearing in range A2:A9.  Before these names, there are abbreviations such as Dr., Mr. Mrs. etc.  To worsen the situation, there may/may not be a space after the abbreviation.  A sample dataset may look like this:

Mr.Ramakrishna
Mr Ramakrishna
Mr. Ramakrishna
MrRamakrishna
PhD.Saurav
Dr (Mrs.) Indu Sharma
Dr. (Mr) Rakesh Singh
Mr. Mrinal Joshi

The task is to extract only the names in another column.

You may refer to my solution in this workbook.

Append data from alternate columns of the same table

{0 Comments}

On the Data worksheet, data for payments received from different customers is shown invoice wise.  Since payments are received in tranches, they are shown in different columns - column C and D show dates and amounts received in the first tranche respectively and column E and F show dates and amounts received in the second tranche respectively.

One may want to generate the list of Invoice Number, Customer Name and the Amount for a particular date.  Since dates are split across two columns, it will not be possible to filter the dataset.  In order to filter the dataset, one will have to append data from alternate columns.

I have been able to accomplish this by using Power Query - a free add-in from Microsoft for Excel 2010 and higher versions.

You may refer to my solution in this workbook.

Compute “running total in” across years in a Pivot Table

{0 Comments}

Assume quantity sold date by date and City in a three column database.  The objective is to determine year wise, month wise and City wise running total of quantity sold in a Pivot Table.

The issue which will arise with generating this result in a Pivot Table will be that the Show Values As > Running Total in, resets the quantity sold to 0 when the year changes.

This issue can be overcome by writing DAX formulas in a Power Pivot.  You may refer to my solution in this workbook.

Story telling with Excel Power BI

{6 Comments}

With Power Business Intelligence (BI) tools of Excel 2013, one can metamorphose raw data and/or results of complex calculations into stunning and interactive visualizations.  Power View (one of the four components of Power BI) allows one to create a PPT like flow in Excel thus allowing one to weave a story.  To be able to interact with/create visualizations, you will need to install Microsoft Office Professional Plus 2013 (this version will already have two of the four components of Power BI - PowerPivot and Power View).  Additionally, you will have to install the following add-ins from Microsoft (the other two components of Power BI)

1. Power Query; and
2. Power Map

I have tried to showcase the prowess of Power BI tools of Excel 2013 in these two workbooks:

1. An overview of the BRIC Economies
2. Sales data analysis

Just in case you do not have Microsoft Office Professional Plus 2013, you can see the features of Power BI in this 10 minutes video of the first workbook - "An overview of the BRIC Economies".

Compute configuration count using Set Theory and Venn Diagrams

{0 Comments}

Assume a table which lists attendees for a Company's Annual day function.  In this Table, data for every attendee is shown on a separate row so if an employee attends the function with his/her spouse and three children, then there will be 5 rows for that employee.

The question is to determine the count of the following family configuration:

1. Employees only (those who attended without spouse and children); and
2. Employees, spouse and children (Family); and
3. Employees and spouse (no children); and
4. Employees and children (no spouse)

You may refer to my solution in this workbook.  I have solved this problem using Set Theory and Venn Diagram.

Append data from two worksheets with different structures

{0 Comments}

Assume a file with two worksheets.  The first worksheet has data in column A:H (8 columns) and the second one has data from B:J (9 columns).  One may want to create a third worksheet with specific columns of data appended from both worksheets - the columns to be shown in the third worksheet are not  at the same position on both worksheets i.e. while the Inventory Date data is in column A on sheet1, it is in column H on sheet2.

You may refer to my solution in "Resultant Data - final" worksheet of this workbook.

I have solved this problem using Power Query - a free add-in from Microsoft for Excel 2010 and higher versions.  The result is dynamic and refreshable (just as in a Pivot Table).

Perform a lookup with inexact text strings and/or spelling mistakes

{21 Comments}

Any lookup function i.e., VLOOKUP(), LOOKUP(), INDEX()/MATCH(), HLOOKUP() requires the text lookup variable (the first input of the VLOOKUP(), HLOOKUP() etc. ) to be spelled exactly the same as the entries in the lookup column.  If the spelling does not match, the result is #NA.  To some extent, inexact searches can be performed by concatenating wildcard characters (? and *) to the lookup variable.

The task is to increase MS Excel's tolerance towards spelling mistakes and inexact text matches across two tables. For e.g., when performing a lookup, somehow MS Excel should be able to understand the Delhi City is the same as City, Delhi.  In order to perform, inexact searches, you can download and install Microsoft's Fuzzy lookup add-in from here.

I have run this add-in on two live datasets in this workbook.