[Hockeynow70]

Greetings,

I need the forum's help again. I've tried everything I can think of but 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 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.

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,
Chris

[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.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2