Hello all, I hope you can help. I have queried a database using MSQuer
to create a worksheet with over 28000 clientID's for around 50000 records
The clients can be identified by their ID# in Col A. Each client is eligibl
for one or more programs for a certain time period. Elig from date is in col I,
Elig To Date is in Col J. The Program code is in Col H. About half of
them are listed in several rows (up to around 8) because they are or hav
been eligible for more than one program. I need to create a new worksheet
with a list of unique clientId's with the most current FSEligToDate
I know it can probably be done with formulas, but because of the number o
records, I'd like to do it with code. I'm pretty new to VBE & don't know where
to start. Any help would be greatyl appreciated.

ClientId FundingSourceId FS.EligFromDate FS.EligToDat
83108 4 20030101 2005032
83108 15 20030101 2003063
83108 16 20030102 2003123
83103 15 20030101 2003022
83118 15 20030103 2003123
83118 16 20030818 2004091
83118 22 20030103 2005010

The Results of which would be

ClientID FundingSourceID FSEligFromDate FSEligToDat
83108 4 20030101 2005032
83103 15 20030101 2003022
83118 22 20030103 2005010

I'm using XL2000 & WindowsNT. Thanks in advance
Please post to NG.

Sort your data on fsEligToDate column, Descending, so the most recent dates
are at the top.

In the next available column, put in a formula like

You example doesn't match your description of column locations, but suspect
it is abbreviated. You have program 16, but show no results, assume this is
an oversight.

Assume column M is the next available column. Headers in row1 and first
date record in row2
Assume ID in column A, Program in column H and elig to date in col J


in M2 put the formula

=Sumproduct(--($A$2:A2=A2),--($H$2:H2=H2))
drag fill this down the column or if Column L has no blanks, select M2,
double click on the black square on the lower right of the outline to have
it automatically fill to the end of your data. This will progressively
count.

Now select A1 and do Data=Filter=Autofilter
select column M and filter on the value 1.

Select all you data, do edit=Copy, then go to a1 on a new sheet and do
Edit=PasteSpecial and select Values.

Repeat the paste Special with formats if you want the formats.

You can sort the new data on Id and program, delete column M.

--
Regards,
Tom Ogilvy




"Eric Smith" wrote in message
...
Hello all, I hope you can help. I have queried a database using MSQuery
to create a worksheet with over 28000 clientID's for around 50000
records.
The clients can be identified by their ID# in Col A. Each client is
eligible
for one or more programs for a certain time period. Elig from date is in
col I,
Elig To Date is in Col J. The Program code is in Col H. About half of
them are listed in several rows (up to around 8) because they are or have
been eligible for more than one program. I need to create a new
worksheet
with a list of unique clientId's with the most current FSEligToDate.
I know it can probably be done with formulas, but because of the number of
records, I'd like to do it with code. I'm pretty new to VBE & don't know
where
to start. Any help would be greatyl appreciated.

ClientId FundingSourceId FS.EligFromDate FS.EligToDate
83108 4 20030101 20050323
83108 15 20030101 20030630
83108 16 20030102 20031231
83103 15 20030101 20030228
83118 15 20030103 20031231
83118 16 20030818 20040916
83118 22 20030103 20050103

The Results of which would be:

ClientID FundingSourceID FSEligFromDate FSEligToDate
83108 4 20030101 20050323
83103 15 20030101 20030228
83118 22 20030103 20050103

I'm using XL2000 & WindowsNT. Thanks in advance.
Please post to NG.