Tags: MMULT

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 →

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 →

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 →

Create employee wise Effort Utilisation Report

{ 0 Comments }

Assume a “Labour utilisation” report which shows day wise tasks performed by different employees (row labels are task names, column labels are days and data in the matrix shows employee names).  Each employee can perform multiple tasks on the same day and same task on multiple days. Given a list of all employees, one may want to answer […]

Read More →

Count entries in a range which exclude certain user defined words

{ 5 Comments }

Assume the following sentences in range B2:B6 B2 – This is an Apple Pie B3 – An apple a day keeps a doctor away B4 – These Pears, apples and mangoes are sweet B5 – In this season, prices of mangoes have increased B6 – This is a glass of Guava juice In range B9:B10, type […]

Read More →

Count word combinations in individual columns of a multi column database

{ 6 Comments }

Assume a database of five rows and four columns.  Each cell of the database has some text value – let’s call this the source grid.  In another part of the same worksheets, the text values of source grid have been arranged as rows and column headings – let’s call this the destination grid. The task […]

Read More →

Analysing customer walkin data by date and service taken

{ 5 Comments }

Assume data is in range A3:E10.  In A4:A10, various service types are mentioned.  In B3:E3, dates are mentioned from June 1, 2012 to June 4, 2012.  In range B4:E10 are numbers representing number of customers.  One may want to answer the following questions from this data: 1. For every date, total number of customer walkins and […]

Read More →