Sorting by rows - How can I speed up the process?

[Data Cruncher]

Hello,

I have a large series of individual numbers in 5 columns, running down to 574 rows, including the headers.

The data, which comprises numbers from 1 to 45 inclusive in each row, has been entered without being put into numerical order (aaaarrrgh!!!!), so filtering for specific combinations of numbers is impossible.


A small sample:-

,Col1,Col2,Col3,Col4,Col5
Row1,29,16,5,14,20
Row2,11,30,32,22,19
Row3,7,35,26,18,22


I have worked my way through to row 166 so far, selecting the data in each of the 5 columns, 1 row at a time, and using the Data>Sort>Continue with the current selection>Sort by Row # [Ascending]>Options>Sort left to right settings.

This works perfectly, but it only works on 1 row at a time, and I need to be able to do this quickly. If I try to sort by 3 consecutive rows at a time, it sorts row 1 correctly, but the following 2 are sorted depending on the values in row 1, so that is of no use.

I have two spreadsheets to fix this way, the other has 1426 rows with 6 columns then another 2 separate columns, so the job is horrendous.

Is there any way I can set the data up, or use a Macro, to sort each row independently of each other? Or simply ask it to sort by multiple rows?

I will be most appreciative of any help anyone can offer me.

Many thanks,

Data Cruncher
Victoria, Australia

 

[Keith Nichols]

G'day DataCruncher,

I have had a 20 minute play with this and can see that it gets horrible when I try to sort the rows by repetetive Min and Max functions and ifs. Way too complicated.

Without dedicating any more time to thinking about this, I think that you should be able import the spreadsheet into new database and do the manipulation there by way of a report as you should be able to treat every record individually.

Someone with far more smarts than I have could probably develop a macro to repetitavly apply your sorting criteria in the spread sheet as it stands but I wouldn't know where to start.

Sorry I'm not able top post a solution for you.

 

[Data Cruncher]

Thanks for trying Keith. Fingers crossed, someone can dream up a Macro for me or tell me how to lay the data out to make it easier.

Data Cruncher.

 

[unmarkedhelicopter]

Try This :

 

[Data Cruncher]

Many, many thanks. This works perfectly. It's just what I needed. You're a champ.

Data Cruncher