Consolidating data

  • Thread starter Thread starter Danny J
  • Start date Start date
D

Danny J

Hi folks,

I have a huge number of records (approx 140,000). These represent patients
attending a doctor's clinic. I am trying to harvest the data and obtain
figures for how many times each patient has attended the clinic. I could
use Countif but I do not want to add 5000 different patients as the lookup.
Suggestions welcome!

Thanks,

Danny
 
Danny J,

Break the data up into manageable chunks on separate sheets. Then select
one of the chunks, and use Data | Pivot Table. When you create your pivot
table, drag the name first to the row fields, and then again to the data
items. That will create a count of each individual name. Repeat that for
each chunk. Then copy the pivot tables and paste them as values into
another sheet, and combine them into one table. Then use Data | Pivot Table
again, but this time, drag the name to the row, and the count to the data
area, and use sum.

HTH,
Bernie
MS Excel MVP
 
Thanks


Bernie Deitrick said:
Danny J,

Break the data up into manageable chunks on separate sheets. Then select
one of the chunks, and use Data | Pivot Table. When you create your pivot
table, drag the name first to the row fields, and then again to the data
items. That will create a count of each individual name. Repeat that for
each chunk. Then copy the pivot tables and paste them as values into
another sheet, and combine them into one table. Then use Data | Pivot
Table again, but this time, drag the name to the row, and the count to the
data area, and use sum.

HTH,
Bernie
MS Excel MVP
Click to expand...
 
Back
Top