I have a large number of sets of data (500000) consisting of 9 column
of values each. I want to sort on ALL columns .
Using the Range.Sort method I can only specify 3 columns to sort on.
How can I sort on all columns

--
JackRn
-----------------------------------------------------------------------
JackRnl's Profile: http://www.excelforum.com/member.php...fo&userid=3717
View this thread: http://www.excelforum.com/showthread.php?threadid=56980

first sort on the columns of least importance, then sort again on the other columns

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"JackRnl" wrote in message
...
|
| I have a large number of sets of data (500000) consisting of 9 columns
| of values each. I want to sort on ALL columns .
| Using the Range.Sort method I can only specify 3 columns to sort on.
| How can I sort on all columns?
|
|
| --
| JackRnl
| ------------------------------------------------------------------------
| JackRnl's Profile: http://www.excelforum.com/member.php...o&userid=37172
| View this thread: http://www.excelforum.com/showthread...hreadid=569801
|

Thanks for the reply,

Indeed a possibility and I did that already, but the disadvantage i
that it takes MUCH more time.
In my app the calculations take 17 seconds and sorting takes 4 second
for each sort. I actually timed and results are
0 sorts 17 seconds
1 sort 21 seconds (columns 1-2-3)
2 sorts 25 seconds (columns 4-5-6 followed by 1-2-3)
3 sorts 29 seconds (column 7 followed by 4-5-6 and 1-2-3)
and I might have more columns in the future, so you understand I woul
like to avoid sorting several times and would be pleased with a faste
solutio

--
JackRn
-----------------------------------------------------------------------
JackRnl's Profile: http://www.excelforum.com/member.php...fo&userid=3717
View this thread: http://www.excelforum.com/showthread.php?threadid=56980

This is the official Microsoft recommendation. I don't think there is anything you can do that makes it faster, certainly not try
own VBA macros.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"JackRnl" wrote in message
...
|
| Thanks for the reply,
|
| Indeed a possibility and I did that already, but the disadvantage is
| that it takes MUCH more time.
| In my app the calculations take 17 seconds and sorting takes 4 seconds
| for each sort. I actually timed and results are
| 0 sorts 17 seconds
| 1 sort 21 seconds (columns 1-2-3)
| 2 sorts 25 seconds (columns 4-5-6 followed by 1-2-3)
| 3 sorts 29 seconds (column 7 followed by 4-5-6 and 1-2-3)
| and I might have more columns in the future, so you understand I would
| like to avoid sorting several times and would be pleased with a faster
| solution
|
|
| --
| JackRnl
| ------------------------------------------------------------------------
| JackRnl's Profile: http://www.excelforum.com/member.php...o&userid=37172
| View this thread: http://www.excelforum.com/showthread...hreadid=569801
|

Sometimes I concatenate several values into one column to accomplish
this.

That is, in your 10th column enter the fomula A1&B1&C1&.... then sort
by that column.

You may have to convert the values to text if they are of varying
lengths.

That is text(A1,"#####")& ...

Best Regards

Jim Palmer


Niek Otten wrote:
This is the official Microsoft recommendation. I don't think there is anything you can do that makes it faster, certainly not try
own VBA macros.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"JackRnl" wrote in message
...
|
| Thanks for the reply,
|
| Indeed a possibility and I did that already, but the disadvantage is
| that it takes MUCH more time.
| In my app the calculations take 17 seconds and sorting takes 4 seconds
| for each sort. I actually timed and results are
| 0 sorts 17 seconds
| 1 sort 21 seconds (columns 1-2-3)
| 2 sorts 25 seconds (columns 4-5-6 followed by 1-2-3)
| 3 sorts 29 seconds (column 7 followed by 4-5-6 and 1-2-3)
| and I might have more columns in the future, so you understand I would
| like to avoid sorting several times and would be pleased with a faster
| solution
|
|
| --
| JackRnl
| ------------------------------------------------------------------------
| JackRnl's Profile: http://www.excelforum.com/member.php...o&userid=37172
| View this thread: http://www.excelforum.com/showthread...hreadid=569801
|

<certainly not try own VBA macros.
I meant writing your own sort mechanism.
However, you could record a macro which does the subsequent sorts automatically.
You'll gain the time needed to select menus etc, which will probably more than regain the sort time.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Niek Otten" wrote in message ...
| This is the official Microsoft recommendation. I don't think there is anything you can do that makes it faster, certainly not
try
| own VBA macros.
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
| "JackRnl" wrote in message
| ...
||
|| Thanks for the reply,
||
|| Indeed a possibility and I did that already, but the disadvantage is
|| that it takes MUCH more time.
|| In my app the calculations take 17 seconds and sorting takes 4 seconds
|| for each sort. I actually timed and results are
|| 0 sorts 17 seconds
|| 1 sort 21 seconds (columns 1-2-3)
|| 2 sorts 25 seconds (columns 4-5-6 followed by 1-2-3)
|| 3 sorts 29 seconds (column 7 followed by 4-5-6 and 1-2-3)
|| and I might have more columns in the future, so you understand I would
|| like to avoid sorting several times and would be pleased with a faster
|| solution
||
||
|| --
|| JackRnl
|| ------------------------------------------------------------------------
|| JackRnl's Profile: http://www.excelforum.com/member.php...o&userid=37172
|| View this thread: http://www.excelforum.com/showthread...hreadid=569801
||
|
|

Thanks for the rapid replies (again),

Certainly I will not write my own macros for that, I know you can't win
compared to the build in code using an interpreter like VBA.
It is a pitty Array.Sort has not been implemented , would help a LOT of
people I think.
For identifying if a set is unique while filling the sets I already
created a key consisting of a "concatanated" string (actually summing
the values of the columns each multiplicated by a constant specific for
that column) and considered sorting on them. I guess I will need to do
that as I found nothing else.

Thanks for the help


--
JackRnl
------------------------------------------------------------------------
JackRnl's Profile: http://www.excelforum.com/member.php...o&userid=37172
View this thread: http://www.excelforum.com/showthread...hreadid=569801