Excel - A macro to sort dates and remove duplicates

December 2016




Issue


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.

Solution


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


=SUM(IF(FREQUENCY(IF((LEN(DATES & PHARMA) * (PHARMA=E13)) >0,MATCH(DATES & PHARMA,DATES & PHARMA,0),""), IF((LEN(DATES & PHARMA) * (PHARMA=E13)) >0,MATCH(DATES & PHARMA,DATES & PHARMA,0),""))>0,1,0)) 


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.

Related :

This document entitled « Excel - A macro to sort dates and remove duplicates » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.