Tags: ADVANCED FILTERS

Generate a list of missing invoice numbers

{ 2 Comments }

In a range of cells, assume there is a list of invoices raised during a certain period.  Since adequate care was not taken to number the invoices sequentially, there are missing invoice numbers in the Excel range.  So, it is possible that after raising invoice number 501, 502 and 503 the next invoice raised was 507. Given […]

Read More →

Filter on a column of Date and time values

{ 4 Comments }

Assume a three column database – Site ID, Reason & Date and time.  The data is available in range A2:C6 of sheet1 of this workbook.  The objective is to view all those records where Mains failed between 23:00 and 03:00 (irrespective of the date). Applying a Custom Filter > Between on the Data and time column […]

Read More →

Split data from a master document into various worksheets based on a template sheet

{ 22 Comments }

Assume a base data sheet with information for various vendors.  There is also a worksheets which has the template of the reconciliation statement which is sent to all vendors.  At the click of a button, one may want vendor worksheets to be created from the template. Furthermore, one may want to do either of the […]

Read More →

Extract farthest/latest date based on multiple conditions

{ 0 Comments }

Assume a three column database showing Site ID, Customer, Status and Requested Date.  On the same site ID, the same customer may have different status on different dates.   In such a scenario, one may want to know the farthest/latest requested date and its corresponding status for all unique combinations of Site ID and Customer. I initially attempted to […]

Read More →

Remove duplicates after satisfying additional conditions

{ 3 Comments }

Assume a two column database of patient ID’s and service availed.  One patient may avail the same service multiple times in a year due to which that record may appear as many times as the service is availed.  For e.g., if patient A001 avails the Radiology service twice, then A001 and Radiology will appear in two rows. […]

Read More →

Extract data based on customer specific dimensions

{ 0 Comments }

Depending upon customer requirements specified for width, thickness and length os material, extract a report showing all records from the master stock-list which meets the requirements. The question and solution have been elaborately explained in this workbook.

Read More →

Extract information based on background colour

{ 0 Comments }

A range of cells has multiple background colours.  Depending upon the colour selected by an end user via a cell drop down, those cells (along with information in other columns) should get transferred to another worksheet. You may refer to my solution in this workbook.

Read More →

Sequencing using advanced filters

{ 0 Comments }

Assume a three column database of Article Number, Size and Quantity.  The size column may have five sizes – Small, Medium, Large, XL and XXL.  Not all sizes will be available for every article number.  The task is to create a three column database in another worksheet, which shows only those article numbers having all […]

Read More →