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

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

 

[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.