Tags: ARRAY FORMULA

Combine unique entries from a range of cells after satisfying a condition

{ 2 Comments }

Here is a simple three column dataset showing Item ID, Catalogue Name and Catalogue Year Item ID Catalogue Name Catalogue Year A Spring 2012 A Spring 2016 A Spring 2017 B Fall 2014 B Fall 2014 B Fall 2016 The second and third last rows are clear duplicate rows. The task is to create a […]

Read More →

Prepare an invigilation schedule for each teacher by different time periods

{ 5 Comments }

Imagine a multi column exam invigilation schedule with the following information S. No. Name of staff Designation of staff member Two columns for each day on which there is an exam – one for Morning and another for Afternoon A * under each column if that particular staff member has to be an invigilator during […]

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 →

Sum the largest 5 of the last 10 numbers in a row ignoring blanks

{ 2 Comments }

Assume a single row of data with numbers and blanks appearing at random intervals.  The objective is to sum the largest 5 of last 10 numbers in that row.  Solving this problem entails multiple steps: Identify the last 10 numbers in that row i.e. starting from the right hand side, identify the last 10 numbers […]

Read More →

Convert a text entry into its number equivalent

{ 0 Comments }

Consider this simple two column table showing text entries in column A and the corresponding numbers in column.  Assume this data is in range A2:B11 (headings are in A1:B1). text Value A 1 B 2 C 3 D 4 E 5 F 6 G 7 H 8 I 9 J 0 The objective is to […]

Read More →

Summarise data with multiple wildcard OR conditions

{ 0 Comments }

Imagine a dataset as shown below Status Text Number Active bat 1 Passive erq 2 Passive cat 3 Active enm 4 Active dog 5 Suppose the data is in range A3:C7 (headings are in A2:C2).  The objective is to add numbers where the following conditions are met: 1. Status should be Active; and 2. Text […]

Read More →

Remove special characters from a string

{ 4 Comments }

Hi, Assume a column of names as follows: Name Mohammed Zia-Ul Haque Steven Thomas – ,-Rohit Sunil Ahir-Chowdhary.- Anuj ———– Sameer — ..,Mohit — Rajeev Nair. Monalisa . Das Vijeta … –,.Anjana. M.U..,- Please observe that there are special characters before the name, within the name and after the name.  The task is to remove […]

Read More →

Analyse all possible combinations of cheques received and identify the combination which gives maximum benefit to the Customer

{ 11 Comments }

Scenario 1 In a Stock Broking business (Firm), Customers deposit margin money through cheques. Although cheques are received from customers at branches, clearing (realization) of the same normally takes 3-4 days.  On one hand, the Firm wishes to allow trading to the customers against the deposited cheques from the day it receives the cheque, on other hand there is […]

Read More →