Parse Excel 2007 data to build list of "unique"

[SueWithQuestion]

THANK YOU!

For a number of reasons, I cannot use the Excel function to "Remove Duplicates", and I really need some help to do that kind of thing.

Attached is a sample file with some dummy data that is representative of my data. My actual data set can have anywhere from 30,000 to 50,000 rows of data.

I'm basically trying to create a list of unique records from my list of records in the worksheet Raw Data.

The "Record Number" field is unique, EXCEPT if the Manager field = Smith. If the Manager field = Smith, then Record Numbers are re-used on an annual basis. This means in the Unique List I want to keep only the record number with the latest date within a calendar year.

In my worksheet, Unique List, I only need a subset of the fields from the Raw Data Sheet, as shown in my attached sample file.

Can someone help me with this?

[tom1977]

the uniqe list You can do with formula from attachment the rest you can do with index and match formula

[SueWithQuestion]

I don't see any formulas in the attachment. I also don't know how to use INDEX and MATCH to do this.

I'm afraid I still need help!

[tom1977]

Ok. I did once again this time combination of macro and array formula
unfortunately I am afraid that could be little slow with 3000 rows of data
Formula is in column D

[SueWithQuestion]

I just realized this WONDERFUL macro is not doing exactly what I needed. It is not doing the exception processing for records that have the Manger field = Smith:

"...The "Record Number" field is unique, EXCEPT if the Manager field = Smith. If the Manager field = Smith, then Record Numbers are re-used on an annual basis. This means in the Unique List I want to keep only the record number with the latest date within a calendar year."

In other words I should be seeing duplicate Record Numbers, but ONLY for SMITH, and one of the numbers can be in one calendar year, and the other must be in a different calendar year.

I hope I'm explaining that clearly.

Any chance you can help me by modifying the macro you developed to handle this? I tried to do it myself, but didn't even come close to figuring it out. I'm REALLY hoping you can help me again!!!

[tom1977]

please send a attachment with row data and the result table (made by hand) and I will try to change my macro/formula in the evening (evening in my part of globe of course )