Count number of trips when duplicate entries [Solved/Closed]

Report
-
venkat1926
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
-
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
http://www.speedyshare.com/...

2 replies

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
789
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
http://www.speedyshare.com/
remove the password.
give the name of the url in your posting
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
rizvisa1
Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
756
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.

http://www.speedyshare.com/...
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
789
Keith

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