Part Number Sorting..... Help :)

[renojimmyjam]

I have tried everything I can think of to accomplish this sort. At this time I have spent more time trying to figure out the sorting of my document than it would have taken to re type it.

Here is my problem:

I have part numbers that are either solid numbers or alphanumeric. When Excel sorts the numbers it sorts the raw numbers and then starts to sort the alpha numeric numbers from left to right.

EG:
[TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]192[/TD]
[/TR]
[TR]
[TD="class: xl65"]750[/TD]
[/TR]
[TR]
[TD="class: xl65"]752[/TD]
[/TR]
[TR]
[TD="class: xl65"]775[/TD]
[/TR]
[TR]
[TD="class: xl65"]998[/TD]
[/TR]
[TR]
[TD="class: xl65"]1001[/TD]
[/TR]
[TR]
[TD="class: xl65"]1004[/TD]
[/TR]
[TR]
[TD="class: xl65"]1005[/TD]
[/TR]
[TR]
[TD="class: xl65"]1007[/TD]
[/TR]
[TR]
[TD="class: xl65"]1001A[/TD]
[/TR]
[TR]
[TD="class: xl65"]1001ABM[/TD]
[/TR]
[TR]
[TD="class: xl65"]1001E[/TD]
[/TR]
[TR]
[TD="class: xl65"]1001EB[/TD]
[/TR]
[TR]
[TD="class: xl65"]1009A[/TD]
[/TR]
[TR]
[TD="class: xl66"]100AM[/TD]
[/TR]
[TR]
[TD="class: xl65"]1010N[/TD]
[/TR]
[TR]
[TD="class: xl65"]1011D1[/TD]
[/TR]
[TR]
[TD="class: xl65"]1011D41[/TD]
[/TR]
</tbody>[/TABLE]
Notice that the 100AM is sorted after the 1009A and should be sorted before the 192 at the top.

Thanks for any help I could get on this one.

 

[diddi]

hi and welcome to the forum

assume data in column A
if you made a helper column ("B") containing =VALUE(A1)
that would give numeric part, and that sort both columns on col B

 

[Teethless mama]

Assuming your data start in A2:Axxx

B2: =IF(ISNUMBER(A2),A2,LOOKUP(1E+99,--LEFT(A2,ROW($1:$99))))

C2: =SUBSTITUTE(A2,B2,"")

Select B2:C2 and copy down as far as needed


Select A2:Cxxx and sort column B, then column C. Delete both columns B & C after you finished sorting.

 

[renojimmyjam]

Hey your awesome!! Teethless, But I still have one other problem that I found.

Half of my part numbers start with a letter also. Your method worked perfect for the numbers with aft letters but it gives an error with the pre letters. Is there a solution to this problem? Thanks Again!!

[TABLE="width: 192"]
<colgroup><col style="width:48pt" width="64"> <col style="width:48pt" width="64"> <col style="width:48pt" width="64"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]1639[/TD]
[TD="width: 64, align: right"]1639[/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl65"]1640[/TD]
[TD="align: right"]1640[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl65"]1761B[/TD]
[TD="align: right"]1761[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD="class: xl65"][/TD]
[TD="class: xl66"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl65"]A1000DA[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="class: xl65"]A1001BH[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="class: xl65"]A1001C2[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="class: xl65"]A1001DH[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="class: xl65"]A1001EH[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="class: xl65"]A1011-5N[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="class: xl65"]A1011D1[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
</tbody>[/TABLE]

 

[renojimmyjam]

Also to complicate the problem some of the part numbers contain TWO letters if the front.

[TABLE="width: 64"]
<colgroup><col style="width:48pt" width="64"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]X1055JK[/TD]
[/TR]
[TR]
[TD="class: xl65"]X999[/TD]
[/TR]
[TR]
[TD="class: xl65"]XL7
[/TD]
[/TR]
</tbody>[/TABLE]