Tags: COUNTA

Unpivot data with a formula

{ 1 Comments }

Many a times, one may want to convert a column expanding data to a row expanding one (also referred to as “Unpivoting/flattening a dataset”. The easiest way to do so is with the usage of Power Query (referred to as the Query Editor in some versions). The technique to unpivot a dataset with the help […]

Read More →

Segment customers into dynamic buckets

{ 0 Comments }

Consider a 4 column table – Respondent ID, Device ID, App Name and Category.  So this dataset shows which apps are installed on which device ID by which user and which category do the apps fall into.  It is a small dataset with only 4 columns and 2,000 rows. The question on this dataset is […]

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 →

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 →

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 →

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

Read More →

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

Read More →

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

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

Read More →

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

Read More →