Tags: ISERROR

Dynamically extract unique values from a filtered range

{ 0 Comments }

Data in a two column database (A3:B13) can be Auto filtered on column A with one or many conditions.  Data in column B will be numbers only (positive, negative or 0’s) – no text values. After filtering data in column A, one may want to extract the unique numbers from column B to cell A18 (and then […]

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 →

Consolidate data from a specific worksheet of multiple workbooks to multiple worksheets of one workbook

{ 8 Comments }

Assume there are multiple files saved in a specific folder.  There can be Excel, Word, PDF, PowerPoint and other files types saved in this folder.  Furthermore, Excel files can have following extensions – .xls,.xlsx.xlsm.  Each Excel file has multiple worksheets with one worksheet being named Sheet1.  While data on sheet1 of all Excel files will start […]

Read More →

Generate a list of all Excel files from a specific folder without using VBA

{ 56 Comments }

Assume a folder named Test on the desktop.  In this Test folder, there is another folder named Survey reports.  In the Survey reports folder, there are Word documents, PowerPoint files, Excel files, PDF files and all sort of other files types. In a new workbook, one may want to list down all Excel files from […]

Read More →

Extract number from an alphanumeric string

{ 72 Comments }

Given an alphanumeric string, one may want to perform the following Extract phone number from the string Assume a list of customer addresses with multiple phones numbers mentioned in the address field itself.  These numbers may be mobile numbers and/or mobile numbers.  Furthermore, PIN codes may also be mentioned in the address string. One may want […]

Read More →

Programmatically transfer data from master sheet to sub sheets with conditions

{ 43 Comments }

A user inputs raw material purchase data in a master sheet.  This data is entered by date and vendor.  One may want to view individual vendor sheets at a monthly level.  In the attached file, i have presented two solutions – a macro based one and a formula based one. The data layout and instructions […]

Read More →

Sum visible cells of a filtered range ignoring errors

{ 4 Comments }

In a multi column database, assume a filter has been applied on some columns.  Post filtering, some error values (such as #DIV/0!, #NA etc.) appear in the one of the numeric columns.   The objective is to sum numbers in a numeric column.  The usual SUM() function would not work because the range of sum function should be error free.  Furthmore, […]

Read More →

Extract a report showing parameters beyond tolerance limits

{ 0 Comments }

Depending upon tolerance limit specified for every technical parameter, extract a report which lists down all those day wise parameters which breach tolerance limits.  The question and solution have been elaborately explained in the workbook.

Read More →