Tags: MATCH

After filtering a dataset, allow the user to display only specific columns in the result

{ 2 Comments }

Here is a simple 5 column dataset showing basic employee information. The objective is to show only those rows of data in which the Score (column D) is greater then 3.  While one can solve this with a simple filter, the solution will not be dynamic.  To get a dynamic solution, one may use the […]

Read More →

Determine the top selling location for each product

{ 0 Comments }

Visualise a 3 column dataset as shown below – Location, Product and Sales.  Each location can have multiple products (Product A has Banana, Apple and Carrot) and each product can be sold in multiple locations (Banana is sold in locations A, B and F). The objective is to determine the location with highest sales for […]

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 →

Determine number of learners who have completed different stages of multiple online courses

{ 4 Comments }

Here is a sample dataset of learners who have cleared different stages of multiple courses on offer within an Organisation: Learner Stage completed Course Bill Stage 1 Public Speaking Bill Stage 2 Public Speaking Bill Stage 3 Public Speaking Susan Stage 1 Effective Communication Bob Stage 1 Public Speaking Bob Stage 2 Public Speaking Sheila […]

Read More →

Return best possible fit, to manually entered dimensions, with the intent to minimise wastage

{ 5 Comments }

Refer to a simple 5 column representative inventory dataset of a Glass manufacturer: Model Length (MM) Wide (MM) Thk (MM) CAT HX9-G-ARD 1071 273 3.5 A MYP-G-3RD 580 535 3.2 B EPO-G-3RD 580 535 3.2 A MYG-G-3R 966 350 3.2 A MYN-G-3RD 649 530 3.2 A GM SPIN-G-3FD 882 395 3.2 A MY8-G-AR 880 400 3.5 B […]

Read More →

Generate a list of assignees for different projects based on a competency matrix

{ 4 Comments }

Here is a small sample of a Project matrix which shows tasks to be accomplished for various projects.  There can only be upto 6 tasks per project. Project Name Task1 Task2 Task3 Task4 Task5 Task6 Project1 Painting Chef Gardener Project2 Tiling Digging Engineering Project3 Mechanic Engineering Here is a competency matrix showing the competencies of employees on […]

Read More →

Compute potential Sales of a retail outlet

{ 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 →

Extract City, State and Pin code from an address string

{ 51 Comments }

For a lot of Excel users, a common problem is to extract City, State and Pin Code from an address string.  What compounds the issue is that there is no standardisation in an address string.  For e.g., one may end the address with a Pin code, while others may end it with a State and Country.  […]

Read More →

Prioritise investment liquidation to minimise Capital Gains

{ 2 Comments }

Assume a five column dataset with the Fund Name in column A, Market Value in column B, Gain/Loss in column C, Gain/Loss % in column D [column C/column B] and Type of investment in column E (Short term or Long term).  Since multiple investments can be made in one fund, there can be multiple line items […]

Read More →

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

Read More →