Remove duplicates after satisfying additional conditions


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.

Leave a Comment

Your email address will not be published.


  • Hi Ashish,

    in the same file, lets say we enter another data entry of patient who availed one/multiple service, than the advance filters wont auto refresh. Is there any way to make it dynamic? (some forums asked to use VBA, ans solution without using it?)

    • Hi,

      Sorry about my incorrect reply earlier. One need not use Power Query to resolve this issue. In addition to the “Array formula and advanced Filter” solution, I have shared the following two solutions in the workbook (please download the revised workbook from the original blog article)

      1. Only array formula solution; and
      2. PowerPivot solution.

      Hope this helps.