I have a project based on retrieval of different branded goods by a number of people on numerous occasions and at varying intervals between retrieval. I currently have it set up as follows:
colum A: Patient ID number
Column B: Date good was supplied to them (typically a one month supply but may vary)
column C: Collection period ( time from receiving the first lot of goods to time receiving last lot of goods (in days)) - I need to calculate this based on the date they first received and last received the particular good
column D: Name of the branded product
column E: the quantity of the good picked up by the person (units for this is days)
What I need to be able to do is to write a formula that will allow Excel to recognize that If the patient Id in column A is the same (each person has a unique id number) and if the name of the product is the same then I want to calculate the difference in the date between the first and last time they received said good to give me the collection period for that person for that good. The dates and goods received vary from person to person making this a little more tricky on myself.
After calculating the collection period for each product for each recipient I then need a formula which will allow me to see how many days worth of goods each person recieved for each particular good. This will involve designing a formula which tell excel that if the patient ID is equal and if the product name is equal then calculate the sum of the number of days supplied (column E) of said good to the person