Serious Problem with GIANT spreadsheet

[JAverill]

Okay, I hope someone here can help.
Heres my problem:
I have a huge spreadsheet with 2 columns, and 36,000 rows. The two columns are "Part Number" and "Job Date".
The Part Number column is alpha-neumeric.
For many of the part numbers, there are several duplicate rows with the same part number, but a different date in each.
I need to delete these duplicate rows, keeping only the one with the most recent date for each part number.
I haven't the slightest clue how to go about this, short of manually selecting and deleting each duplicate row, except the newest.
PLEASE tell me that there is an easier way.

Thank you,
John

 

[mikerickson]

1) Sort the data by part number ascending, date by date descending
2) Use Advanced Filter, unique only, to copy the data to a different location.
if your orginal data is in A:A and B:B and you copied to D:D, then put

=INDEX(B:B,MATCH(D2,A:A,0),1) in E2 and fill down.

D:E has your cleaned data.

 

[iliace]

Not terribly serious

What I did is created some mock data - about 10,000 records. In column C, I added a formula to find the latest date for the current row's part number. In column D, I added a formula to return Yes or No on whether the date in column B matches the date in column C. Now, you can filter column D for "No", and delete those rows.

Note, however, that for the 10,000 rows, column C took about 5 minutes to calculate.

Excel Workbook
	A	B	C	D
1	Part Number	Date	Max Date	Match?
2	FFF114Z	6/15/2008	6/15/2008	Yes
3	SSSS119II	4/30/2008	5/31/2008	No
4	PPP118C	4/24/2008	4/24/2008	Yes
5	TTTT111J	4/15/2008	4/15/2008	Yes
6	OOOO117YY	4/26/2008	4/26/2008	Yes
7	YYYY113CC	7/26/2008	7/26/2008	Yes
8	OOO119O	5/14/2008	5/14/2008	Yes
9	UUU112XX	3/11/2008	3/11/2008	Yes
10	JJJ119V	2/28/2008	6/4/2008	No
Sheet1

 

[JAverill]

Thank you both very much for your help, that problem has been solved, so naturally another one has popped up.
I was hoping you wouldn't mind helping me with one more situation.
Now my problem is with merging data. I have two different excel spreadsheets, one that contains part number and last date used (the one from the previous problem), and one that contains part number and die number. The problem is that they are not all the same part numbers, I.E. some parts may be missing from one list that are on the other.
Somehow, I need to match the last date used data to the die number via the matching part number.
This is totally beyond my experience. Any help would be greatly appreciated

 

[iliace]

Using my approach, you can accomplish this with a VLOOKUP formula.

Let's say on Sheet2 you have the die and a part number that's contained on the list in Sheet1. You use this formula to get the corresponding value in the max date column:

=VLOOKUP(Sheet2!A1, Sheet1!$A$1:$C$100063, 3, FALSE)