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 PowerPivot add-in, then a calculated column formula can resolve this problem.
Solution for all versions of MS Excel
I have shared two solutions here:
1. Array formula and advanced filters; and
2. Only array formulas
For better understanding of the question and to view the final solution, please refer to this workbook.
Remove duplicates after satisfying additional conditions
{ 3 Comments }