Tags: ISNUMBER

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

{0 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 FILTER() dynamic array function that comes with the Microsoft 365 subscription service.

In cell G2, one may simply write this formula

=FILTER(A2:E9,D2:D9>3)

This is a far better solution because it is formula driven and thereby dynamic.  So all good till here.  Now let's make it a little interesting.

The revised objective is to show only those rows of data in which the Score (column D) is greater then 3 and only display 3 columns - Name, Department and Score (columns 1,3 and 4) in the end result.  This can be solved using Data > Advanced filter but the result will be static.  To get a dynamic solution, one may use a nested FILTER() function in cell G2

=FILTER(FILTER(A2:E9,D2:D9>3),{1,0,1,1,0})

This formula will return the same number of rows (3 rows) as the previous FILTER() function returned with only 3 columns - Name, Department and Score.  The 1's and 0's in the formula denote whether one would like to see the particular column in the end result or not.  So once again, all good till here.

Now, let's take it a notch higher.  What is there were 35 columns in the original dataset and one wanted to see data appearing in columns 1,6,22,25,29 and 34.  It will be quite time consuming to enter the 1's and 0's in order within the FILTER() function.  So the question here is how does one save time and effort?  Ideally one should be able to just enter the column headings one wants to see in the end result.

As you can observe in the image above, one has to simply specify the columns to extract in column C and the result populates from column K rightwards and downwards.  Using dynamic array formulas and the FILTER() function, one saves effort in entering 1's and 0's in the FILTER() function (the FILTER() function has been written in cell K2 - download link of the file is shared below).  If one types Address in cell I5, then Address would automatically appear in cell N1 and so will the entries in range N2:N4.  So this does seem like a good solution.  So while it is good, it is not a perfect solution.  In column I, if one changes the order of the headings i.e. one enters Name, Dept, Address and Score (rather than Name, Dept, Score and Address), the result under columns M and N would be incorrect (see image below).

Solving this challenge led me to using Data > Get & Transform.  I made use of the latest feature introduced in Power Query called "Data Types" (I received this feature update on December 4, 2020).

As one can see in the image above, one simply has to select any heading one wants and that appears in the next available column.

You may download my solution workbook from here in which i have shown both formula based and the Power Query solution.

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 5 x x
Function 6 x

Here is a user created input table of his/her requirements.  Let's assume that this data is in range A12:B18 (including the header row)

Functions Input
Function 1
Function 2 x
Function 3 x
Function 4
Function 5 x
Function 6

The expected result is the "Product" which meets the user defined function combinations. The result should be Product B. If there are different products which meet the user's requirements, only the first one will be returned as a result. If one would like all products to be returned, the one can use Power Query to resolve this problem.

=IFERROR(INDEX($B$2:$E$2,1,MATCH(COUNTA($A$13:$A$18),MMULT(1*(TRANSPOSE(B3:E8=B13:B18)),1*(ISNUMBER(ROW(INDIRECT("1:"&COUNTA($A$3:$A$8)))))),0)),"No such product")

Please note that this is an array formula so please confirm the formula with Ctrl+Shift+Enter.

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 should contain either b or c or d

So the result for the dataset above should be 6 i.e. 1+5.  Sounding like a simple case of summarising based on multiple conditions, one may attempt using the SUMIFS() function here.  However, a single SUMIFS() function will not work because a SUMIFS() can only accept multiple AND conditions (not OR conditions).  The only way to make the SUMIFS() function work is to use 3 of them and then add them up.

So this SUMIFS() function will work

=SUMIFS($C$3:$C$7,$A$3:$A$7,A11,$B$3:$B$7,B11)+SUMIFS($C$3:$C$7,$A$3:$A$7,A12,$B$3:$B$7,B12)+SUMIFS($C$3:$C$7,$A$3:$A$7,A13,$B$3:$B$7,B13)

A11:A13 has Active and B11:B13 has *a*,*b*,*c*.

While this looks like a solution, the problem is that there will have to be one SUMIFS() function for each OR condition.  So the more the number of OR conditions, the more the SUMIFS() functions and the lengthier the formula.

In this workbook, I have shared two alternative solution.  One uses the DSUM() function and other uses a combination of MMULT(), TRANSPOSE() and SUMPRODUCT() functions.

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 Mango and Apple.  The objective is to count the sentences in range B2:B6 which exclude the words mentioned in range B9:B10.

The array formula (Ctrl+Shift+Enter) for getting the count of exclusions would be:

=SUMPRODUCT(1*(MMULT(TRANSPOSE(1*(ISTEXT($B$9:$B$10))),1*(ISERROR(SEARCH($B$9:$B$10,TRANSPOSE($B2:$B$6)))))=COUNTA($B$9:$B$10)))

The result should be 1.

The array formula (Ctrl+Shift+Enter) for getting the count of inclusions (sentences which include these words), would be:

=COUNTA($B$2:$B$6)-SUMPRODUCT(1*(MMULT(TRANSPOSE(1*(ISTEXT($B$9:$B$10))),1*(ISERROR(SEARCH($B$9:$B$10,TRANSPOSE($B2:$B$6)))))=COUNTA($B$9:$B$10)))

The result should be 4.

One can also solve the problem with the PowerPivot tool.  You may refer to my PowerPivot solution in this workbook.

Performing an iterative lookup to return closest match

{5 Comments}

Suppose a worksheet name is Code_details.  The following values lie in range A2:A14 of this sheet:

A12,AS478,QW447,EQ46,RYT9985,VCX147,BNM159,ASQ478,VC325,ASW675,A123,
ASDFG1234567890 and ASDFG123456789

Suppose there is another worksheet named Summary where the following values lie in range B5:B7:

ASQ47809876,ASW675458 and QWERT

Now one may want to lookup ASQ47809876 in A2:A14 of Code_details sheet.  If this exact value is not found there, then the lookup value should be trimmed by one digit/character from the extreme right i.e. the lookup value should now become ASQ4780987.  The search should carry on till an exact match is found.

You may refer to my solution in this workbook.

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 total number of services taken; and
2. For every date, new customer walkins and new services taken; and
3. For every date, repeat customer walkins and repeat services taken

While the first and third questions are fairly straight forward to solve, some deliberation would be required for the second question.  A new service type taken on June 3, 2012 would be one that has not been taken by any customer from June 1 - 2, 2012.  So if cell A8 has Service type E and cell D8 (data for June 3, 2012) has 3 (3 customer took service type E on June 3, 2012), then this service should be counted only if there is no figure in range B8:C8 i.e. no customer took this service on June 1 - June 2, 2012.

Depending upon the version of MS Excel which you are using, there could be two ways to solve this problem

Solution for MS Excel 2010 and higher versions

If you are using the Power Query add-in and the PowerPivot add-in, then a few simple steps and minimal DAX formulas can solve this problem.  The result will be dynamic and refreshable (just as in a Pivot Table).

Solution for all versions of MS Excel

While this solution works for all versions of MS Excel, it uses an array formula (Ctrl+Shift+Enter).  Array formulas, if used extensively in the workbook, adversely effect the system's performance.

You may refer to my solution in the this workbook.

Calculate turn around time excluding Sundays and public holidays

{206 Comments}

Assume a two column database showing starting date/time and ending data/time (Data/time stamp appear in a single cell).  Given a list of public holidays in a year and starting and ending work times, one may want to know the turn around time excluding Sundays and public holidays.

You may refer to my solution in this workbook.