Matching problem

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

Hi,

I have a spreadsheet where I have over 35K part numbers in the first
column and revision dates of those parts in the second column. The
part numbers in the first column will appear multiple times, based on
the number of revisions that were made against it. I've converted the
dates to numbers hoping to find a way to filter out the part numbers
in the first column that have the highest number date in the second
column. Essentially, I need to come up with a list that only contains
the current revison of these parts and get rid of the rest.

Sample:

I have this...

PN Date No.
007-0566 37595.21
007-0566 37735.29
007-0567 37595.21
007-0567 37735.29
007-0569 37522.25
007-0569 37553.19
007-0569 37995.36
007-0570 37522.25
007-0570 37553.19
007-0570 37995.36
007-0572 37522.24
007-0572 37995.36
007-0576 37995.36
007-0577 37522.24
007-0581 37593.48

And need this result...

007-0566 37735.29
007-0567 37735.29
007-0569 37995.36
007-0570 37995.36
007-0572 37995.36
007-0576 37995.36
007-0577 37522.24
007-0581 37593.48

Any help would certainly be appreciated....

Thanks,

Steve
 
Here's one way, I assume you have sorted by first PN and then the dates (and
times) ascending, try this instead. Sort by PN ascending and Date No
descending, will give you this instead

PN Date No
007-0566 37735.29
007-0566 37595.21
007-0567 37735.29
007-0567 37595.21
007-0569 37995.36
007-0569 37553.19
007-0569 37522.25
007-0570 37995.36
007-0570 37553.19
007-0570 37522.25
007-0572 37995.36
007-0572 37522.24
007-0576 37995.36
007-0577 37522.24
007-0581 37593.48


this means that the first occurrence of the PN will also have the latest
revision, assume the data (not the headers) start in A2 with the dates in
B2, in C2 put


=COUNTIF($A$2:A2,A2)


copy down 35000 rows by select C2 again and move the cursor to the lower
right corner of C2 and when the cursor changes to a thick cross double click
to copy down the formula.


Now select the whole table from A1 to C35000, you can do that by typing A1:C
35000 in the namebox above the column A header and pressing enter. Then do
data>filter>autofilter,

from the dropdown in C1 select 1, now select the visible range and copy and
paste it unto a new worksheet. Finally remove the help column and format as
date and time

I did a little test on your sample and it returned exactly the result you
posted


You can also do this using advanced filter, there you can actually copy the
filtered list directly somewhere else
 
Steve,

You can do it in two steps:
assuming the parts are in column A and dates are in column B

1. create a third column with the following array formula
=IF(MAX((RangeOfPartNumbers=$A1)*(RangeOfDates))=$B1,1,0)
This column will be flagged as 1 wherever the max date occurs for each
part number

2. Advance filter the third column for 1, that's your condensed list
 
Just another option, you can do it with a Macro:

Sub StripIt()
Dim LastRow As Long
Dim x As Long

LastRow = Cells(Rows.Count, 1).End(xlUp).Row

With Range(Cells(1, 1), Cells(LastRow, 2))
.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2") _
, Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1, MatchCase:=
_
False, Orientation:=xlTopToBottom
End With

Application.ScreenUpdating = False
For x = LastRow To 3 Step -1
If Cells(x, 1).Value = Cells(x - 1, 1).Value Then
Range(Cells(x, 1), Cells(x, 2)).Delete Shift:=xlUp
End If
Next x
Application.ScreenUpdating = True


End Sub

It assumes that the Headers are in A1:B1.

Try it on a *copy* of your data

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Just another option, you can do it with a Macro:

Sub StripIt()
Dim LastRow As Long
Dim x As Long

LastRow = Cells(Rows.Count, 1).End(xlUp).Row

With Range(Cells(1, 1), Cells(LastRow, 2))
.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2") _
, Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1, MatchCase:=
_
False, Orientation:=xlTopToBottom
End With

Application.ScreenUpdating = False
For x = LastRow To 3 Step -1
If Cells(x, 1).Value = Cells(x - 1, 1).Value Then
Range(Cells(x, 1), Cells(x, 2)).Delete Shift:=xlUp
End If
Next x
Application.ScreenUpdating = True

End Sub

It assumes that the Headers are in A1:B1.

Try it on a *copy* of your data

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk











- Show quoted text -
Click to expand...

Thanks to everyone for their solutions.... I actually tried all of
them to see if one was any faster than the other. It seems like they
all took about the same amount of time to generate because of the
large amount of data that I'm working with...

Thanks again,

Steve
 
Back
Top