ExcelBanter - Macro question

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Macro question (https://www.excelbanter.com/excel-programming/314317-macro-question.html)

I need help with first figuring out whether or not this is possible
and second writing the macro. I have a very large report, 13000
records. There are six columns: account #, name, birthdate, date of
visit, diagnosis, and location. There are many duplicate records.
For example, there may be ten records for Jane Doe. One record has a
date of visit of 10/12/2004 and nine records have a date of visit of
10/13/2004. I need to delete 8 of the nine records with the date of
10/13/2004. I then need to count the number of records with the same
account #. Is this possible?

Thanks in advance for all of your help!!!!

KrisG.

--
Patrick Molloy
Microsoft Excel MVP
---------------------------------
I Feel Great!
---------------------------------
"G-MAN" wrote in message
om...
I need help with first figuring out whether or not this is possible
and second writing the macro. I have a very large report, 13000
records. There are six columns: account #, name, birthdate, date of
visit, diagnosis, and location. There are many duplicate records.
For example, there may be ten records for Jane Doe. One record has a
date of visit of 10/12/2004 and nine records have a date of visit of
10/13/2004. I need to delete 8 of the nine records with the date of
10/13/2004. I then need to count the number of records with the same
account #. Is this possible?

Thanks in advance for all of your help!!!!

KrisG.

send me your sheet - say a few hundred rows & I'll return a solution.

Method : use a class to hold the account and date
use a scripting dictionary to hold the classes. A dictionary has an Exist
method on the keys so that you can easily check if a key exists
use Account number as the key.
the .count property of the class will indicate how many visits per a/c
there are.
Its quite simplistic & I'll post the code too.

regards

--
Patrick Molloy
Microsoft Excel MVP
---------------------------------
I Feel Great!
---------------------------------
"G-MAN" wrote in message
om...
I need help with first figuring out whether or not this is possible
and second writing the macro. I have a very large report, 13000
records. There are six columns: account #, name, birthdate, date of
visit, diagnosis, and location. There are many duplicate records.
For example, there may be ten records for Jane Doe. One record has a
date of visit of 10/12/2004 and nine records have a date of visit of
10/13/2004. I need to delete 8 of the nine records with the date of
10/13/2004. I then need to count the number of records with the same
account #. Is this possible?

Thanks in advance for all of your help!!!!

KrisG.

Patrick, where can I find your email address so I can send the file to
you? My email address is gman3177 "at" yahoo.com

Thanks,
KrisG

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!