Sorting Revision Labels in Excel

[Hockeynow70]

Greetings,

I need the forum's help again. I've tried everything I can think of bu
can't get Excel to do what I want (I don't know how to write VBA yet).

We have revision controlled documents and I need to sort large lists t
determine, for example, the latest Revision in the list. To do this,
need Excel to sort by Part Number, then by the Revision in the orde
they are shown below. The Revision value is located in a cell adjacen
to the Part Number.

Can anyone help? I'm running Excel 2010 on Win7. Thanks in advance.

Here is a sample of how I need Excel to sort (ascending downward):

[Part #] [REV]
LO2300 --
LO2300 0
LO2300 1
LO2300 A
LO2300 A1
LO2300 B
LO2300 B1
LO2300 AA
LO2300 AB
LO2300 BA
LO2301 --
LO2301 0
LO2301 1
LO2301 A
LO2301 A1
LO2301 B
LO2301 B1
LO2301 AA
LO2301 AB
LO2301 BA


Warm regards,
Chri

 

[Claus Busch]

Hi,

Am Fri, 13 Dec 2013 17:32:15 +0000 schrieb Hockeynow70:

We have revision controlled documents and I need to sort large lists to
determine, for example, the latest Revision in the list. To do this, I
need Excel to sort by Part Number, then by the Revision in the order
they are shown below. The Revision value is located in a cell adjacent
to the Part Number.

try in C2:
=IFERROR(IF(CODE(LEFT(B2))>57,CODE(LOWER(LEFT(B2)))+1000000+IF(LEN(B2)=1,0,MID(B2,2,5)/1000),IF(CODE(RIGHT(B2,1))

64,LEFT(B2,LEN(B2)-1)*1000+CODE(RIGHT(LOWER(B2),1)),B2*1000)),B2)

and copy down.
Then sort by Part Number and helper column


Regards
Claus B.