How to custom sort a long file with new entries.

[skippy1]

I have an excel file file about a thousand entries that we number like following (please copy/past this in new sheet)

Index	Item Number
1​	F1
2​	F2
3​	F3
4​	F3.1
5​	F3.2
6​	F3.3
7​	F3.4
8​	F3.5
9​	F3.6
10​	F3.7
11​	F3.8
12​	F3.9
13​	F3.10
14​	F3.11
15​	F4
16​	F5
17​	F5.1
18​	F6

I would like to sort by Item Number column with ability that I can add new entries in the same format and be able to sort it again correctly.

Right now this sheet is created in sorted way.

The issue I am facing is my actual sheet has many columns. When I sort the list on a different column, how can I sort it back on Item Number column based like it is shown here?

Thanks!

 

[James006]

Hi,
If I correctly understand your question, you could use the Column Index ...

 

[skippy1]

Hi,
If I correctly understand your question, you could use the Column Index ...

I don't have Index column in my actual worksheet. I provided this only in this sample.

So If I sort my sheet to anything else, I lose the original sorting and I can't come back to it.

 

[James006]

OK ...
Can you insert a Column ... say Column A or use your first empty Column Z ...
Add header Index
in cell below type =row()-1
and copy down ... before a Copy / Paste Values for this Column which will become your "Sort Buoy"

 

[skippy1]

OK ...
Can you insert a Column ... say Column A or use your first empty Column Z ...
Add header Index
in cell below type =row()-1
and copy down ... before a Copy / Paste Values for this Column which will become your "Sort Buoy"

This was my work around, I had an extra column for sort and when I added a new row that is F3.10, I would add value 3.10 there and this were a encountered this problem between .9 and .10. Now 3.10 is smaller than the previous 3.9 so it messes the sorting.

I was wondering if there is any clever way where I can just sort on the original column because dropping F and retaining the reset doesn't work when values reaches great then .9

 

[James006]

Hi,
Another suggestion to create your Index Column

=SUBSTITUTE(A2,"F","")*1