Custom Sorting

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a column that has blanks, characters, numbers, number w/ text attached
and text. When I sort (selecting sort with numbers and numbers stored as text
separately) the order items are returned in is : blanks, characters, numbers
and numbers w/ text mixed together, then text. I want to be able to sort so
my numbers and numbers w/ text appear first. Please advise if possible.

For example:
Sorting: current sort
#
(this is a blank)
123
123A
HOME


Desired Sort:
123
123A (these two can be interchangeable)
#
HOME
(blank) (these bttm 3 can be interchangable)

TFTH
 
select the whole range first and sort ascending
this will give you ,,, numbers,,,,numbers with text,,,text,,,spaces
 
Could you use MID and VLOOKUP functions in the column immediately adjacent
[let's say Column A] to your column to be sorted [Column B]? In your VLOOKUP
Table Array [Columns C & D], column C would be the numbers 0 through 9, all
symbols, all letters, a blank space and 'HOME'. Column D would be the sort
order you desire, from 1 to whatever.

Then have a formula in column A =VLOOKUP(MID(column B address,1,1),name of
your VLOOKUP range,2,FALSE). If it should become important for HOME to be
next-to-last, then you could include an IF statement.

Sort by column A values, then COPY/PASTE your resorted list from column B to
wherever you need it. dak
 
Back
Top