Special Sorting - Non-Alphabetical - Please Help

[jbillh]

Hi Everyone,

I have several hundred cells that I need sorted. The problem is, if I sort them alphabetically, it is in the wrong order. Here are examples of my data:

PC1000-BKS-750lb-MS
PC1000-BKS-550lb-MS
PC1000-CG-750lb-MS
PC1000-CG-550lb-MS
PC1000-DC-750lb-MS
PC1000-DC-550lb-MS

If I sort them A-Z, that is how the list comes out. If I sort them Z-A, it is reversed. Is there a way for me to sort a long list of these and have the following result?

PC1000-BKS-750lb-MS
PC1000-CG-750lb-MS
PC1000-DC-750lb-MS
PC1000-BKS-550lb-MS
PC1000-CG-550lb-MS
PC1000-DC-550lb-MS

In this list, the data is sorted by 750's before 550's and alphabetically.

Thank you very much for your help!

Adam

 

[cooper645]

No problem,

The easiest way to do this is to use two helper columns.
This solution assumes
a. the number always comes after the second "-"
b. the letters always come after the first "-"

I assume the cells to sort start in cell A1.

insert two columns (B & C)

in cell B1 enter the formula (this extracts the numbers element)

=TRIM(LEFT(SUBSTITUTE(MID(A1,FIND("|",SUBSTITUTE(A1,"-","|",2))+1,LEN(A1)),"-",REPT(" ",LEN(A1))),LEN( A1)))

in cell C1 enter the formula (this extracts the letters)

=TRIM(LEFT(SUBSTITUTE(MID(A1,FIND("|",SUBSTITUTE(A1,"-","|",1))+1,LEN(A1)),"-",REPT(" ",LEN(A1))),LEN( A1)))

copy the formulas down until your last row of data.

now go to sort and filter > select custom filter > then sort Column B on Values from Z to A
then add anther element and sort column C on values from A to Z

Job done, simply hide the columns again.

note: if you are opposed to having the columns in the middle of the table, perform the steps to fill the formula in, then cut and paste the columns across the sheet out of the way, then apply the filter on the new position of the columns.


HTH

Coops

 

[jbillh]

Thanks for your help, Coops!

One other thing I forgot to mention is that this list also has a quantity associated with each of the items. The list looks more like this:

SKU Quantity
PC1000-BKS-750lb-MS - 100
PC1000-BKS-550lb-MS - 200
PC1000-CG-750lb-MS - 0
PC1000-CG-550lb-MS - 150
PC1000-DC-750lb-MS - 250
PC1000-DC-550lb-MS - 0

It also goes on for 200+ lines. Another thing is, we generate a list like this each week (with different quantity numbers) and would like to be able to sort them quickly each time.

I don't know if this changes how you would solve the issue, but if so, I would appreciate your input.

I'll try your current solution and post here about how it works.

Thanks Much,

Adam

 

[cooper645]

just pasted the new samples straight over the old stuff in column A, works a treat and sorts itself immediately. (no need to remove the formulas or sorting)

The extra info makes no difference as it looks for the first and second hyphens to extract the info.

If your having problems let me know I will try to steer you in the right direction


Edit: with regards to copying the formula down, I don't want you to suck eggs but if you double click the bottom right corner of the cell after entering the formula, it should copy it down the sheet to the last row. https://www.youtube.com/watch?v=3L5DTFQEOP4. <--- video showing process

 

[jbillh]

Thanks again for your reply, I can see that is working. I did do the copy/paste formula through my whole sheet as well before your last reply.

I do have some experience with Excel, but I'm certainly not as good as you are!

Thanks Again,

Adam

 

[cooper645]

Jolly good, glad you managed to get it all to work.

Coops