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 →
{ 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 →
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 →
{ 2 Comments }
Assume data in range D6:E11 – months in D6:D11 and numbers in E6:E11. Headings are in D5:E5. On filtering multiple values in column D, one may want to view the filtered criteria in cell (separate criteria by comma for multiple selections in auto filter drop down). For solving this problem, you will have to download and install the morefunc […]
Read More →
{ 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 →
Remove duplicates after satisfying additional conditions
{ 3 Comments }