I Have large Data and I need to consolidate the Patient with condition

[hatahetahmad]

Hello Everyone,
I everyday get large (over 10,000 rows) data in one table that i want to consolidate patient by date

Names in : patient
Consolidated column : billed, approved, collected, adjusted
Filtered or Summed by : servdate

Like :

Patient | servdate | billed | approved | collected | adjusted
SURNAME, FIRST_NAME | 6/24/2016 | 885.33 | 295.11 | 289.21 | 590.22

\\ I Don't Mind VBA Solution or advanced filter but please I Need A Solution

{confidential data removed - per OP's request}

 

[bhos123]

What do you want to do, use Pivot tables if you want to summerize.

 

[hatahetahmad]

I did use Pivot Table and i made distinct count for patient But Pivot table gives me wrong Grand total

 

[MikeGG]

A pivot table seems to work correctly as follows:
Insert-Pivot table
In the first dialogue box, ensure that the correct range was selected and click OK
In the pane at the right, drag Patient to Rows
Click on Billed, Approved, Collected, Adjusted; by default you get rows with Total of these per patient
(Don't click on Trandate unless you are going to have more than one date per spreadsheet. If you are, drag it to Columns.)

 

[hatahetahmad]

Yeah, I found the problem thank you all
in my large data pivot table summerize distinct count and delete duplicates
if patient come in june and come again in july pivot table grand total add 1 but subtotal add 1 to june and 1 for july so it equal 2 in sub and 1 in grand

i'll make formula that count duplicte and summe it with grand