Find max value in one column based on another column

[Dave]

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

 

[Mike H]

Maybe

=LOOKUP(2,1/(A1:A100=C1),B1:B100)

Where C1 is the part No you are looking for.

Mike

 

[T. Valko]

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 Edit>Copy
Then, Edit>Paste Special>Values>OK

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.

 

[Dave]

Mike,

This "kind of" solved my problem, but not quite. The LOOKUP formula did
return the highest revision number (in column C) for the part numbers in
column A. I thought then I could filter the part number column and get all
the latest revisions for the part numbers.

But the formula "broke down" at row 4319 (of 61,228 rows) and started
returning each revision in column B again.

At part number 1330500 the formula started returning all revisions from
column B, instead of the max revision as with part number 1330496:
1330493 A00 A00
1330494 A00 A01
1330494 A01 A01
1330495 A00 A01
1330495 A01 A01
1330496 A00 A01
1330496 A01 A01
1330497 A00 A00
1330498 A00 A00
1330499 A00 A00
1330500 A00 A00
1330500 A01 A01
1330503 100 100
1330503 A00 A00
1330504 100 100
1330504 A00 A00
1330505 100 100
1330505 A00 A00
1330506 100 100
1330506 101 101

Any idea why this happened? Is it my PC's memory? I have 3GB RAM.

 

[Mike H]

Dave,

The formula I gave you didn't return anything from Column C only Column B.
You may have thought it did because of similarities in the data.

The formula isn't affected by the size of the range except that in Excel
2003 (Don't know about 2007) it won't work on full columns but this is tested
and works on 65535 rows.

=LOOKUP(2,1/(A1:A65535=C1),B1:B6535)

Mike

 

[Dave]

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]

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.

 

[Dave]

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]

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","")

 

[Dave]

Biff,

No, in my example, I just showed what the value was in cell D8. In cell D7
(for the row containing PN 400078) the formula is =IF (A7<>A6,"KEEP","").
All the other rows have the same relative formula.

Sorry for making it confusing....

Dave

 

[T. Valko]

Well, I don't know what's happening. At this point I'm out of "efficient"
suggestions.

If you have 60,000 rows of data what I'm about to suggest will take forever
to calculate. It may even lock-up Excel or your computer.

=IF(COUNTIF(A$1:A$60000,A1)=COUNTIF(A$1:A1,A1),"x","")

Copied down. Personally, I would not attempt that!

What you could do is every so many rows "reset" the formula by resetting the
referenced range at a change in the pn.

For example:

This formula entered in C1:C1000 -

=IF(COUNTIF(A$1:A$1000,A1)=COUNTIF(A$1:A1,A1),"x","")

This formula entered in C1001:C2000 -

=IF(COUNTIF(A$1001:A$2000,A1001)=COUNTIF(A$1001:A1001,A1001),"x","")

Then follow that pattern until your done.

 

[Dave]

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]

OK, good deal! Thanks for feeding back.