analysing big excel data

[resiexfor]

Hello all,
I need your help analysing these data if possible.
This is the number of medications charged on patients in a data range, and arranged per patient or "MR"

Is it possible to know how many total days the patient was charged? like for example MR1 charged first on 16/10/2017 and last charging on 19/10/2017
Meaning number of days is 4.



is there an easy way to do all the MRs without checking one by one?

Also... can it be further analysed by item name afterwards?

Thanks

ITEM NAME	ISSUE DATE	QTY	RATE	MR NO	ENC NO
MEPRA 1GM POWDER SOLUTION FOR INJECTION	16/10/2017	1	43.9900	Mr1	Enc1
PRIZMA 4.5GM POWDER FOR INFUSION	16/10/2017	4	71.2500	Mr1	Enc1
PRIZMA 4.5GM POWDER FOR INFUSION	18/10/2017	2	71.2500	Mr1	Enc1
PRIZMA 4.5GM POWDER FOR INFUSION	19/10/2017	2	71.2500	Mr1	Enc1
CEFAXON 1GM I.V VIAL	26/10/2017	2	48.0500	Mr2	Enc2
CEFAXON 1GM I.V VIAL	27/10/2017	5	48.0500	Mr2	Enc2
CEFAXON 1GM I.V VIAL	29/10/2017	2	48.0500	Mr2	Enc2
CEFAXON 1GM I.V VIAL	30/10/2017	2	48.0500	Mr2	Enc2
MIACIN 500MG/2ML AMPOULE	25/10/2017	5	25.1333	Mr3	Enc3
COLOMYCIN 2MILLION UNITS VIAL	01/10/2017	3	60.0000	Mr3	Enc3
COLOMYCIN 2MILLION UNITS VIAL	02/10/2017	2	60.0000	Mr3	Enc3
COLOMYCIN 2MILLION UNITS VIAL	04/10/2017	2	60.0000	Mr3	Enc3
COLOMYCIN 2MILLION UNITS VIAL	04/10/2017	1	60.0000	Mr3	Enc3
COLOMYCIN 2MILLION UNITS VIAL	05/10/2017	2	60.0000	Mr3	Enc3
ZYVOx 600MG/300ML injection (Linezolid)	05/10/2017	1	301.5800	Mr3	Enc3
COLOMYCIN 2MILLION UNITS VIAL	06/10/2017	2	60.0000	Mr3	Enc3
PRIZMA 4.5GM POWDER FOR INFUSION	07/10/2017	2	71.2500	Mr3	Enc3
COLOMYCIN 2MILLION UNITS VIAL	07/10/2017	1	60.0000	Mr3	Enc3
MEPRA 1GM POWDER SOLUTION FOR INJECTION	08/10/2017	3	43.9900	Mr3	Enc3
MEPRA 1GM POWDER SOLUTION FOR INJECTION	09/10/2017	2	43.9900	Mr3	Enc3
TARGOPLANIN 200MG INJECTION	10/10/2017	4	68.4500	Mr3	Enc3
MEPRA 1GM POWDER SOLUTION FOR INJECTION	10/10/2017	2	43.9900	Mr3	Enc3
TARGOPLANIN 200MG INJECTION	10/10/2017	2	68.4500	Mr3	Enc3
MEPRA 1GM POWDER SOLUTION FOR INJECTION	11/10/2017	2	43.9900	Mr3	Enc3
TARGOPLANIN 200MG INJECTION	11/10/2017	2	68.4500	Mr3	Enc3
TARGOPLANIN 200MG INJECTION	12/10/2017	2	68.4500	Mr3	Enc3
MEPRA 1GM POWDER SOLUTION FOR INJECTION	12/10/2017	2	43.9900	Mr3	Enc3
MEPRA 1GM POWDER SOLUTION FOR INJECTION	13/10/2017	1	43.9900	Mr3	Enc3
MEPRA 1GM POWDER SOLUTION FOR INJECTION	14/10/2017	2	43.9900	Mr3	Enc3
TARGOPLANIN 200MG INJECTION	14/10/2017	2	68.4500	Mr3	Enc3
TARGOPLANIN 200MG INJECTION	15/10/2017	2	68.4500	Mr3	Enc3
MEPRA 1GM POWDER SOLUTION FOR INJECTION	15/10/2017	2	43.9900	Mr3	Enc3
MEPRA 1GM POWDER SOLUTION FOR INJECTION	16/10/2017	2	43.9900	Mr3	Enc3
TARGOPLANIN 200MG INJECTION	16/10/2017	2	68.4500	Mr3	Enc3
TARGOPLANIN 200MG INJECTION	17/10/2017	2	68.4500	Mr3	Enc3
MEPRA 1GM POWDER SOLUTION FOR INJECTION	17/10/2017	2	43.9900	Mr3	Enc3
MEPRA 1GM POWDER SOLUTION FOR INJECTION	18/10/2017	1	43.9900	Mr3	Enc3
TARGOPLANIN 200MG INJECTION	18/10/2017	2	68.4500	Mr3	Enc3
MEPRA 1GM POWDER SOLUTION FOR INJECTION	19/10/2017	2	43.9900	Mr3	Enc3
TARGOPLANIN 200MG INJECTION	19/10/2017	2	68.4500	Mr3	Enc3
MEPRA 1GM POWDER SOLUTION FOR INJECTION	21/10/2017	1	43.9900	Mr3	Enc3
TARGOPLANIN 200MG INJECTION	21/10/2017	2	68.4500	Mr3	Enc3
MEPRA 1GM POWDER SOLUTION FOR INJECTION	22/10/2017	1	43.9900	Mr3	Enc3
TARGOPLANIN 200MG INJECTION	22/10/2017	2	68.4500	Mr3	Enc3
MEPRA 1GM POWDER SOLUTION FOR INJECTION	22/10/2017	2	43.9900	Mr3	Enc3
TARGOPLANIN 200MG INJECTION	23/10/2017	2	68.4500	Mr3	Enc3
MEPRA 1GM POWDER SOLUTION FOR INJECTION	23/10/2017	1	43.9900	Mr3	Enc3
TARGOPLANIN 200MG INJECTION	24/10/2017	2	68.4500	Mr3	Enc3
MEPRA 1GM POWDER SOLUTION FOR INJECTION	24/10/2017	2	43.9900	Mr3	Enc3
MEPRA 1GM POWDER SOLUTION FOR INJECTION	25/10/2017	2	43.9900	Mr3	Enc3
MEPRA 1GM POWDER SOLUTION FOR INJECTION	26/10/2017	6	43.9900	Mr3	Enc3
TARGOPLANIN 200MG INJECTION	27/10/2017	4	68.4500	Mr3	Enc3

<colgroup><col><col><col span="2"><col><col></colgroup><tbody>
</tbody>

 

[kbrummert]

I would create three columns: array formulas for the min and max dates, and a days counter in the third (+1 to be inclusive of the first day in your example). I messed with the columns in my formulas to convert to US dates, but in your case E would be the MrNo column, and G would be the date column.

From there, you could summarize in a pivot table by putting the MrNo and days columns in the rows section.

Be sure to check out array formulas if you are unfamiliar. You will need to use ctrl+shift+enter.


H=MIN(IF($E$2:$E$1000=E2,$G$2:$G$1000))

I=MAX(IF($E$2:$E$1000=E2,$G$2:$G$1000))

=DAYS(I2,H2)+1

 

[resiexfor]

Thanks, That really fixed what I wanted.