Main Forums > The Roundtable

excel sorting, mix of letters/numbers

<< < (2/3) > >>

K Frame:
Ah, so you have to format on a cell-by-cell basis.

Makes sense.

Brad Johnson:
Everything is already formatted as text. If I format the entire column as numbers, Excel converts them to formulae and fubars the whole thing.

It's not a huge problem, more just a PITA. I thought maybe there was a simple fix.

Brad

dogmush:

--- Quote from: K Frame on January 26, 2023, 11:44:25 AM ---Ah, so you have to format on a cell-by-cell basis.

Makes sense.

--- End quote ---

That won't do it by itself.  what you need to do is "extract" the suffix letter from the number, add a value for the suffix, and then sort by the combination.  That's why I said it's clunky.

So let's do an example:  Column A has the mixed up room numbers:
7
5
5c
5a
4
4a
1
3
2a
2

If you just type that into Excel it will auto format the numbers with suffixes as text, and the other as numbers.  Then in Cell B1 you type =SORT(A1:A10) (change the cell numbers to your actual cell range) and auto fill it down.  You will then get this:

7       1
5       2
5c     3
5a     4
4      5
4a    7
1      2a
3      4a
2a    5a
2      5c

Sorted ascending, with numbers first and text second.  In cell C1 you type =IF(ISTEXT(B1:B10),VALUE(LEFT(B1:B10,LEN(B1:B10)-1)),B1:B10)  and autofill it down (again, replace the cell arrays with the appropriate numbers).  This will extract the numbers from the cells with suffixes and asign everything a numerical value.  OUr example now looks like:

7       1      1
5       2      2
5c     3      3
5a     4      4
4      5       5
4a    7       7
1      2a     2
3      4a     4
2a    5a     5
2      5c     5

now that everything is a numerical value, in cell D1 type =RANK.EQ(C1,C$1#,1)  and it'll rank all the cells in Columns C according to their position in the array.  Spreadsheet now looks like:

7       1      1      1
5       2      2      2
5c     3      3      4
5a     4      4      5
4      5       5      7
4a    7       7      10
1      2a     2      2
3      4a     4      5
2a    5a     5      7
2      5c     5      7

Now that everything is ranked, in Cell E1 type =SORTBY(B1#,D1:D10,1) and it will bring the values from column "B" over, but rank them by the values in column "D".  You will now have:

7       1      1      1      1
5       2      2      2      2
5c     3      3      4      2a
5a     4      4      5      3
4      5       5      7      4
4a    7       7      10    4a
1      2a     2      2      5
3      4a     4      5      5a
2a    5a     5      7      5c
2      5c     5      7      7


Highlight and Hide columns B,C, and D and you have your list sorted.  Obviously this is a little clunky for 10 values, but if you have several hundred rooms, it may be worth it.  it's also a pain to add more rooms after the sort.  It's not super handy, but that's how you do it.

WLJ:
Have you tried dropping the spreadsheet into something like LibreOffice and see how it handles it?

Brad Johnson:

--- Quote from: WLJ on January 26, 2023, 01:58:46 PM ---Have you tried dropping the spreadsheet into something like LibreOffice and see how it handles it?

--- End quote ---

I haven't, but it's not a big enough deal to spend a lot of time on. I can live with it as-is, but was hoping one of you reprobates had a point & click solution.

Brad

Navigation

[0] Message Index

[#] Next page

[*] Previous page