Sorting on more then 3 columns

[JackRnl]

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?

[Niek Otten]

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"JackRnl" <[email protected]> wrote in message
news:[email protected]...
|
| 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
|

[JackRnl]

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

[Niek Otten]

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" <[email protected]> wrote in message
news:[email protected]...
|
| 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
|

[Jim P]

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" <[email protected]> wrote in message
> news:[email protected]...
> |
> | 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
> |

[Niek Otten]

<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" <[email protected]> wrote in message news:%[email protected]...
| 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" <[email protected]> wrote in message
| news:[email protected]...
||
|| 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
||
|
|

[JackRnl]

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