Excel - A macro to sort dates and remove duplicates

April 2018


Suppose that I have list of all the prescriptions we got for the year.
  • A new row is created for each medication.
  • Col A shows the date, Col B shows Pharmacy Name, Column C the medication.

If I pick up more than one medication on a trip to that a pharmacy, then my table will show multiple rows with the same date and the same pharmacy name, changing only the medication name in those rows.

I want to count the number of times I went to each pharmacy. A simple count function would work, except if we got multiple medications that day, it may (count) list that pharmacy 3 times (one for each row - remember each row only list one medication) and in Column A it would list the same date 3 times. So when I actually only went their once, it may return an answer of 3, if I just count the column with the pharmacy names.

So how can I count the number of times I went to CVS (e.g eliminating the duplicate dates listed in column A, for that pharmacy when I picked up more the medication on that trip.. It would be a function maybe that returns a count for that pharmacy name, only if it's a unique date that hasn't been counted before.


You can do this :
  • 1. Name the range where the dates of your trip as DATES (in the sample file that would start from cell E29)
  • 2. Name the range where the name of pharmacies are as PHARMA. (in the sample file that would start from cell F29)
  • 3 Once you have done this, go to the list where the names of pharamcies are. Based on your sample file enter the formula given below in cell F13. About formula once you have pasted the formula it is CRITICAL the you DONT not press enter but rather press PRESS CTRL + SHIFT + ENTER AT SAME TIME. If you have done it correctly, you should notice in the formula bar your formula is enclosed in {}. The formula below is based on your sample book


In the formula below E13 was the first row in your sample book. Once you have pasted and then have pressed CTRL + SHIFT + ENTER, drag it down to cover rest of pharma

Note that

Thanks to rizvisa1 for this tip on the forum.
Published by aakai1056. Latest update on September 21, 2011 at 09:37 AM by aakai1056.
This document, titled "Excel - A macro to sort dates and remove duplicates," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (https://ccm.net/).
Excel - How to align all common list?
Excel - Vba inserting row and keep range membership