Author: Ashish Mathur

Flex a Pivot Table to show data for x months ended a certain user defined month

{ 4 Comments }

In this simple 3 column dataset shown below, one can see the month wise demand and energy charge for 2 years – 2017 and 2018. The objective is to compute the month wise demand charge for x months ended a certain user defined Year and Month.  So, if a user selects the Year as 2018, […]

Read More →

Merge 2 work schedules

{ 2 Comments }

In a certain Organisation, assume that there are two work schedules – one that runs from Friday to Thursday and another that runs from Sunday to Saturday.  In the image below, Work schedule 1 is in range A2:C4 and Work schedule 2 is in range E2:G4.  In each dataset, the start date is the first […]

Read More →

Show Project wise status in a Pivot Table

{ 0 Comments }

Visualise a simple 6 column Table as shown below – Project Name and the finish date for each of the 5 stages that the projects go through.  Each project goes through 5 stages – Requirement (Req), Development (Dev), UAT, Implement and Warranty. The objective is to report on the status of each project at the end […]

Read More →

Rearrange a multi heading dataset into a single heading one which is Pivot ready

{ 8 Comments }

Any well arranged dataset should be “Pivot Table” ready with the following 3 important properties: There should be no merged and centered cells; and Every column should have a unique heading; and Every column should have only 1 heading Here’s one dataset which violates all rules mentioned above. Headings in row 1 are merged; and […]

Read More →

Rearrange travel data to clearly show travel from and travel to locations

{ 0 Comments }

Consider a 2 column dataset as shown below User Location AAA Tokyo AAA Osaka AAA Nagoya AAA Hakone AAA Kyoto BBB Sapporo BBB Nara CCC Tokyo CCC Hakone CCC Osaka DDD Osaka DDD Tokyo Customer AAA travelled from Tokyo to Osaka, Osaka to Nagoya, Nagoya to Hakone and Hakone to Kyoto.  All locations appear in […]

Read More →

Identify Customers that Organisations can upsell or cross sell their products to

{ 0 Comments }

Here’s a simple Sales data of a retail Store which sells Apple Products.  Since a customer can transact multiple times, there will be repetitions in the Cust ID column.  While Cust ID 123 and 782 purchased multiple products from the same Store in one transaction, Cust ID 53 purchased multiple products from different stores (Store […]

Read More →

Determine the lowest bidding vendor(s) for each product in a Pivot Table

{ 0 Comments }

Imagine a dataset like this.  This dataset shows vendors that submitted proposals for supplying various parts to a Company.  There is one column for each of the twelve months. Via a simple Pivot Table, one can determine the lowest bidding vendor per product (part) for any chosen month.  However, one may also want to know […]

Read More →

Sort, comma separated entries appearing in a cell, in ascending order

{ 10 Comments }

Assume a single column dataset as shown below.  As you can observe, there are multiple entries appearing the same cell separated by commas. The objective is to sort, in ascending order, the entries in each cell.  The expected result is shown below. I have solved this problem using Power Query a.k.a Data > Get & […]

Read More →

Search for multiple phrases within a cell and extract all those phrases in another column

{ 0 Comments }

Assume a simple two column dataset as shown below.  In column A are some text strings.  In each cell are multiple phrases separated by commas.  In column C are some phrases – one in each cell.  The objective is to search for all phrases in each cell and extract the phrases found in another column […]

Read More →

Show sales only for corresponding months in prior years

{ 2 Comments }

Refer to this simple Sales dataset The objective is to create a simple matrix with months in the row labels, years in the column labels and sales figures in the value area section.  The twist in the question is that for years prior to the current year (2018 in this dataset), sales should only appear […]

Read More →