Count number of trips when duplicate entries

Solved/Closed
Keith - Apr 13, 2010 at 04:43 PM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Apr 14, 2010 at 10:21 PM
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 pharmacy, then my table will show multiple rows with the same date and the same pharmacy names, 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 (ie 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 fucntion maybe that returns a count for that pharmacy name, only if its a unique date that hasn't been counted before. Is that possible?
Thanks Keith
PS I use Excel 2007 for windows (vista)


As requested (thank you) I was taught to upload a file, hope this works
But each time I tried to delete the password it came back after saving the changes,
Here is the password just incase it didn't delete joxericapizo
URL Is
https://authentification.site/files/21938162/Test_File_for_website_help_with_counting_pharmacies.xlsx

2 responses

venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Apr 14, 2010 at 06:43 AM
to understand better can you post a very small extract of the data and also explain what you want with respect to this sample data base. greetings.
upload your sample file in the web
https://authentification.site
remove the password.
give the name of the url in your posting
0
Thank you for teaching me how to upload a spreadsheet. I have done what you requested I think, except I am not sure I eliminated the password or not. Each time I saved the changes after deleting the password , the password kept coming back. So I put the password in my post..
I hope that didn't violate and rules or anything.
I appreciate your help
Thanks Keith
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Apr 14, 2010 at 09:41 PM
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

Here is the sample file with the formula and all.

https://authentification.site/files/21949548/Test_File_for_website_help_with_counting_pharmacies.xlsx
0
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Apr 14, 2010 at 10:21 PM
Keith

rizviza1 has given a solution try that. I do not want to intrude.
0