OK, good deal! Thanks for feeding back.

--
Biff
Microsoft Excel MVP


"Dave" wrote in message
...
Biff,

You were right! I was putting the formula one row too low. My bad!
Works
great now. Thanks for your patience!

Dave

"T. Valko" wrote:

I suspect you're putting the formula in the wrong row. If your data
starts
in A7 then put the formula in C7 then copy down.

=IF(A8<A7,"KEEP","")

--
Biff
Microsoft Excel MVP


"Dave" wrote in message
...
Hi, Biff-

I re-did the formula to specify "KEEP" and got the following result.

The formula used is depicted in column D:
PN REV FORMULA
400078 A KEEP
846261 A00 KEEP =IF(A8<A7,"KEEP","")
876000 A KEEP
876000 B
876001 A KEEP
876001 B
876002 A KEEP
876002 B
876003 A KEEP
876004 A KEEP
876005 A KEEP
876006 A KEEP
876007 A KEEP
876008 A KEEP
The formula depicted is copied down thru Part Number 876008, and
depicts
the
relative cells.

But you'll notice that for PNs 876000 thru 876002 the wrong revision is
depicted as a "KEEP".

I dunno what's happening in my sheet. Your sheet looks good.

Dave

"T. Valko" wrote:

Hmmm...

The results I get are not the same as the results you've posted.

Here's a small sample file that demonstrates this:

xMarkOld.xls 14kb

http://cjoint.com/?gzxPE2vUKA

In column C I've entered the formula but changed the "x" to "Keep"
meaning,
keep this record.

Column D shows the "x" that you posted in your sample. As you'll see
the
"keeps" are in the correct places.

--
Biff
Microsoft Excel MVP


"Dave" wrote in message
...
Biff,

Thanks for the input. This works well, except for the part numbers
which
have only one revision. Then, in those cases, an "X" is placed in
the
column, making the sort be the same as for lower revisions of a part
which
has several revisions.
Example:
TEST A X
TEST B
TEST A00
400078 A X
846261 A00 X
876000 A X
876000 B
876001 A X
876001 B
876002 A X
876002 B
876003 A X
876004 A X
876005 A X
876006 A X
876007 A X
876008 A X
876008 B

In the above exceprt, PNs 876004, 876005, 876006 (which have only
one
revision)have X's where PN 87008 has an X for the lower revision and
a
blank
for the higher revision (B). I need to have 874004,876005,876006
return
the
same value in column C as 876008 revision B.

Thanks,

Dave
"T. Valko" wrote:

Maybe this if you want to get rid of the old data and only keep the
most
recent data (assuming that your data is sorted as is shown in your
sample).

Enter this formula in colun C and copy down to the end of data:

=IF(A3<A2,"x","")

The "x" will mark the latest revision.

Now, convert the formulas to constants:

Select the range of formulas in column C.
Goto EditCopy
Then, EditPaste SpecialValuesOK

Now, sort the entire range on column C in descending order. This
will
put
all the latest revisions at the top.

Find the last "x" and delete everything after it. Then delete
column
C.


--
Biff
Microsoft Excel MVP


"Dave" wrote in message
...
I searched the posts, but didn't find anything obvious that would
work
in
my
situation.

I have a list of part numbers in column A, and their revisions in
column
B.
One part number can have 1 or more revisions. I'd like to find
the
latest
revision for each part number in column A. My list looks like:

PART NUMBER Revision
1120007 A
1120007 B00
5360120 A00
5360120 A01
5360120 D00
and so on

I have 51,000+ rows of data, and would like to summarize by
finding
only
the
latest revisions of the parts- in this case it would be B00 for
PN
1120007
and D00 for PN 5360120. I can probably boil down that 51,000
part
number
rows to 3,000 to 4,000.

I sense it would be pretty simple, but I'm a relative novice when
it
comes
to Excel.

Any help would be greatly appreciated.

Dave