Excel - special sort  
Author Message
PoseiodonsGift





PostPosted: Tue Dec 09 07:00:02 CST 2003 Top

Excel Programming >> Excel - special sort

Hi,

i have different rows, where there are just special values allowed.

for example: one | two | three

if i sort the row, i can just say to sort it ascending one-three-two
or descending two-three-one but i'd like to get one-two-three

How can i realize this problem (in each row, there a a mximum of 10
different names to sort)?

Thanks,
Thomas

Excel217  
 
 
Chip





PostPosted: Tue Dec 09 07:00:02 CST 2003 Top

Excel Programming >> Excel - special sort Thomas,

Create a custom list with three elements (Tools menu, Options,
Custom Lists) and then in the Sort dialog, choose Options and
select this list.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




> Hi,
>
> i have different rows, where there are just special values
allowed.
>
> for example: one | two | three
>
> if i sort the row, i can just say to sort it ascending
one-three-two
> or descending two-three-one but i'd like to get one-two-three
>
> How can i realize this problem (in each row, there a a mximum
of 10
> different names to sort)?
>
> Thanks,
> Thomas


 
 
Torstein





PostPosted: Tue Dec 09 07:02:50 CST 2003 Top

Excel Programming >> Excel - special sort Hi Thomas!
is it possible for you to translate the one - two and three into a new
column with numbers and sort it from that column?

Torstein



> Hi,
>
> i have different rows, where there are just special values allowed.
>
> for example: one | two | three
>
> if i sort the row, i can just say to sort it ascending one-three-two
> or descending two-three-one but i'd like to get one-two-three
>
> How can i realize this problem (in each row, there a a mximum of 10
> different names to sort)?
>
> Thanks,
> Thomas


 
 
Patrick





PostPosted: Tue Dec 09 07:06:09 CST 2003 Top

Excel Programming >> Excel - special sort add a table with all your text values in the first column
and its corresponding decimal in a second column
on your sheet, add a column to the right of your list
then add a VLOOKUP formula, then sort the list based off
the this new column.

In the sub below I assume a column starts at G1 but is of
indeterminate length. I have elsewhere on another sheet a
range called MyTable which is a column of words and a
column of numbers. The words on my worksheet in G1 are in
this list.
The code inserts a new column, ads the formula, sorts the
list then deletes the inserted column...leaving your list
of words sorted as per the values in MyTable

Sub SortList()

Dim Target As Range

Set Target = Range(Range("G1"), Range("G1").End
(xlDown))

Dim cl As Long
cl = Target.Column + 1
Columns(cl).Insert
With Target.Offset(0, 1)

.FormulaR1C1 = "=VLookup(RC[-1],MyTable,2,False)"

End With

With Target.Resize(Target.Rows.Count, 2)

.Sort Target.Offset(0, 1), xlAscending

End With

Columns(cl).Delete


End Sub


Patrick Molloy
Microsoft Excel MVP

>-----Original Message-----
>Hi,
>
>i have different rows, where there are just special
values allowed.
>
>for example: one | two | three
>
>if i sort the row, i can just say to sort it ascending
one-three-two
>or descending two-three-one but i'd like to get one-two-
three
>
>How can i realize this problem (in each row, there a a
mximum of 10
>different names to sort)?
>
>Thanks,
>Thomas
>.
>