Tags: ADVANCED FILTERS

Generate a list of missing invoice numbers

{0 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 the following:

1. Starting and ending invoice number (two numbers) from the invoice book; and
2. List of actual invoice numbers raised during a certain period (as elaborated above)

one may want to generate a list of missing invoices.

You may refer to two solutions in this workbook.

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 (column C) with a value of 23:00 in the "Greater than or equal to" and 03:00 in the "Less than or equal to" would be incorrect because dates are also present in the same column.  The other option would be to use Data > Text to Columns to separate Date and Time into two columns.  If one then applies the same custom filtering criteria on the Time column, nothing would show up - this is because MS Excel will understand 23:00 as greater then 03:00.  If one reverses the custom filter criteria i.e. "Greater than or equal to" as 03:00 and "Less than or equal to" 23:00, the incorrect results would show up.

The solution is to solve this via Advanced Filters.  The steps for solving are mentioned in the file.

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

{16 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 following:

1. Create sheets for all vendors at once;

2. Create individual sheets selected by the end user

You may refer to my solution in this workbook.

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 solve this problem by using a pivot table but the pivot output was incorrect.  The pivot was returning the farthest/latest date for all status' of a particular Site ID and Customer.  Ideally, it should show only the farthest/latest date and its corresponding status for a particular Site ID and Customer.  Therefore, for a particular Site ID and Customer combination, only one row should show up in the final output.  Therefore, the Pivot Table solution did not work.

You may download this workbook for a better description of the problem and my workaround.

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, then a few simple steps (no formulas at all) can solve this problem.  The result will be dynamic and refreshable (just as in a Pivot Table)

Solution for all versions of MS Excel

You may refer my Advanced Filter (along with formula) solution in the workbook.

Remove duplicates after satisfying additional conditions

{0 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.

Once may want to create the following two reports from this database:

1. A list containing all those records where the patient availed just one service; and
2. A list containing all those records where the patient availed more than one service

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, then a few simple steps (no formulas at all) 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 a combination of array formulas (Ctrl+Shift+Enter) and Advanced Filters.  Array formulas, if used extensively in the workbook, adversely effect the system's performance.

For better understanding of the question and to view the final solution, please refer to this workbook.

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 five sizes and their respective quantities.

You may refer to the source data and solution in this workbook.

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, then a few simple steps (no formulas at all) can solve this problem.  The result will be dynamic and refreshable (just as in a Pivot Table).

Solution for all versions of MS Excel

This solution uses the SUMPRODUCT() and Advanced Filters.