Tags: ISNUMBER

Unpivot data with a formula

{ 1 Comments }

Many a times, one may want to convert a column expanding data to a row expanding one (also referred to as “Unpivoting/flattening a dataset”. The easiest way to do so is with the usage of Power Query (referred to as the Query Editor in some versions). The technique to unpivot a dataset with the help […]

Read More →

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 →

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 →

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 →

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

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 →

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

Read More →

SUMPRODUCT function to work on a range with interspersed text values

{ 0 Comments }

In a scenario where text values are interspersed with numeric data columns, the usual SUMPRODUCT function will not work.  One will have to use a combination of array formulas and the SUMPRODUCT function. You may refer to my solution in this workbook.

Read More →

VLOOKUP() function to work only on visible cells of filtered range

{ 12 Comments }

The VLOOKUP() function returns data from a lookup_array irrespective of the filter setting of the lookup_array.  To make the lookup_array of the VLOOKUP() function work only on the visible cells of a filtered range, refer to this workbook.

Read More →