Imagine a two column dataset – Customer Code and Material Number (with alphanumeric data). The objective is to determine the second highest quantity sold per Customer code. Since we will first have to determine the Customer wise and Material Number wise quantity sold, a good way to get started is to use a Pivot Table. […]
Read More →
{ 0 Comments }
Assume a simple matrix like data structure as shown below. As one can observe, for every unique ID, there are duplicate traits appearing. The objective is to remove duplicate traits from each row. The Expected solution is show in the second table. Unique ID Trait1 Trait2 Trait3 Trait4 Trait5 a 1 1 2 b 1 […]
Read More →
{ 2 Comments }
Assume a column with some IP addresses. Each cell has 4 portions separate by a dot. The number of digits in each portion can either be 2 or 3. The following tasks need to be performed on this dataset: 1. Drop the last portion of the string 2. Flip the string 3. Add the following text to […]
Read More →
{ 3 Comments }
Imagine a dataset representing questions answered by various respondents in a survey. The first column has the Respondent’s Name and thereafter there is one column each for a question posed in the survey. The data extracted from the system suffers from the following fallacies: 1. There are blank rows; and 2. Multiple questions answered by […]
Read More →
{ 0 Comments }
Assume a dataset with two columns which lists down the student names in column A and courses opted for in column B. Since one student can opt for multiple courses and the same course can be taken up by multiple students, there can be repetitions in both columns. The objective is to create a matrix […]
Read More →
{ 8 Comments }
The objective is to assist a Store Manager with computing potential sales across different products and colours. To start with let’s assume two datasets: 1. Customer-Colour dataset – a two column table which lists down the colour preference of each customer; and 2. Colour-Product-Price dataset – a three column table which lists down the multiple […]
Read More →
{ 10 Comments }
Assume a simple two column dataset with Name in column A and Department in column B. There are repetitions in the Name column. Here’s a snapshot of the base data: One may want to merge data from multiple cells into a single cell – basically a view where all unique names are listed in a […]
Read More →
{ 0 Comments }
Assume a two column dataset with Date in the first column and Price in the second one. The purpose is to identify times to buy and sell – buying would be just after the lowest low is confirmed and sell before or just after the highest high is in place. Confirmation is achieved through crossover of […]
Read More →
{ 0 Comments }
Assume a simple four column dataset with the following columns – User, Month, Leads and Sales. The dataset shows the user and month wise leads generated and revenue earned. One may want to analyse this data in a Pivot Table with the User field appearing in the Row labels section, Months field appearing in the […]
Read More →
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 →
Filter the Rank Field in a Pivot Table
{ 4 Comments }