want to list only rows with highest value of duplicates from a column

[shane.westmore]

hi all,

Forgive me if this has been posted here. I'm new and am having a huge
problem searching for an answer on the net.
I have a column with item numbers, and another column with letters
listed where each letter is a version of that item number.
For example I might have item number 1234 and versions A, B, C and D.
Each version has its own row in Excel with the same item number.
What I want to do is view the highest version letter for each item
number. In the same sheet I would have several item numbers listed.

For example I would have something like this.
Item number Version
1234 A
1234 B
1234 C
6789 A
6789 B
6789 C
6789 D

What I want to view is the highest version letter of each so that it
would be listed in Excel as follows.
Item number Version
1234 C
6789 D

Can I do this using some sort of filter? If I need some sort of code
to do this then can someone tell me how I can enter this? Any help
would be very appreciated.
Thanks,
Shane

 

[Max]

Try a formulas set up ..

Data in cols A and B, from row2 down

Put in C2: =IF(A2="","",COUNTIF($A$2:A2,A2))
Copy down

Then if you have the item#s listed in say, E2 down
viz in E2: 1234, in E3: 6789, etc

Put in F2, array-enter (press CTRL+SHIFT+ENTER):
=INDEX($B$2:$B$100,MATCH(MAX(IF($A$2:$A$100=E2,$C$2:$C$100)),IF($A$2:$A
$100=E2,$C$2:$C$100),0))
Copy F2 down

Adapt the ranges to suit ..