Need Macro program to create Pivot

[sarat47]

Hello Expert,
I asked this question in this forum and one user suggest me to install software for solution which is not possible.

Can anyone help me whether the solution can be made using Macro programming. Thank you very much in advance.

My Query

I want to create a pivot with list of patients based on specific country.
From the country specific patient, I want to calculate patient with visit and patient without visit based on below principle

--> Column "visit" having value >=1 is will be considered as single visit
--> Column "visit" having value 0 or null will be considered as no visit

Please check the attached excel sheet for reference

[PCI]

--> Column "visit" having value >=1 is will be considered as single visit
--> Column "visit" having value 0 or null will be considered as no visit

Can you detail with Pakistan for example

[sarat47]

Thank you for looking into my query .

My requirement is to create one pivot table in separate tab based on data available in sheet "HA".

i)Count number of patients countrywise, to be populated in column "PID total". E.g. for Pakistan, PID total=5

ii)Count number of patients which underwent visit to the clinic (that means value of visit >0) which should be populated in column "Visit with error"
. e.g. For Pakistan, patient A002, A004 have gone for visit. so Visit with error=2

iii) Count number of patients who have not gone any visit to the clinic (i.e. value of column visit =0), to be populated in column "Visit without error"
e.g. For pakistan, patient A001, A003, A001 have not gone for any visit. so Visit without error=3

iv) Last column "PCT of error free Visit" should calculate percentage of error free visit which means (visit without error/PID total)*100
e.g. For pakistan, PCT of error free Visit= (3/5)*100=60%

Please have a look into attached excel which I modified once again.

Thanks in advance

[PCI]

See attached a poor possibility
2 extra columns are added to help to the calculation
Of course macros could be used to prepared calculation
Note, that in your second file it is not possible to have merged cells for the header to be used with Pivot Table

[PCI]

Another way with macro

Please Login or Register  to view this content.