Excel Sorting Problem...

  daba 23:24 18 Sep 2004
Locked

Need help with sorting left-to-right in Excel 97.

I have a table of numbers, with each row having multiple numbers going left to right, but in random order.

I want to sort the table so that each row has its numbers in ascending order.

The inbuilt sort function doesn't do what I want, as it retains the data relationships, i.e. sorting the top row and moving the data in successive rows accordingly.

This obviously is akin to the column sort, which sorts by one or more key columns, maintaining the relationship of the data.

I realise this may involve some VBA, and wondered if anyone can put me on the right track.

TIA

  VoG II 23:40 18 Sep 2004

A quick workaround.


Copy all the data. Start a new sheet, Edit/PasteSpecial and tick Transpose. Then Sort as normal. Then repeat the Copy and PasteSpecial and transpose to insert the sorted list on the original sheet.

  daba 23:49 18 Sep 2004

Sorry VoG, that's not what I want.

Excel does a sort on the specified column, or columns, or row, or rows, as specified in the dialog, but will move the data in the other highlighted cells... It will not break the relationships while sorting.

My data rows are (except for the fact that they are similar data) not related to each other, so I want to sort the data in row 1 into ascending order, then row 2, etc. up to row n. As I sort each row, I don't want the order of the data in the other rows to be altered.

This needs an iterative loop in vba methinks...

  VoG II 23:58 18 Sep 2004

Sorry, I misunderstood the original question.

It is late and I will have to sleep on this. Hopefully somebody will come up with the solution in my absence ;o)

  daba 00:38 19 Sep 2004

I'm working on it myself obviously in the meantime - but i doubt if my solution will be as elegant as yours VoG. I'll check back tomorrow, cheers.

If it helps, the data is the lottery results download, which tables the draws (rows) and balls drawn (5 columns). Unfortubnately, the drawn balls are in draw order, not numerically ascending order. I need them ascending for my analysis program.

cul8r

  daba 00:40 19 Sep 2004

for 'balls drawn (5 columns)'

read '6 columns'

  VoG II 09:53 19 Sep 2004

Sub test()

Dim lastrow As Integer, irow As Integer

lastrow = ActiveSheet.Cells(Cells.Rows.Count, 1).End(xlUp).Row

For irow = 1 To lastrow

Range(Cells(irow, 1), Cells(irow, 6)).Sort key1:=Range("A" & irow), Orientation:=xlLeftToRight

Next irow

End Sub

  pj123 11:00 19 Sep 2004

Have you tried Data, Sort and enter your Sort By Column. Now select Options, and tick Left to Right and OK.
It should now say Sort By "row"..... and OK it.

  daba 12:00 19 Sep 2004

Thanks VoG, I'll persevere.

pj123. from original post - " The inbuilt sort function doesn't do what I want, as it retains the data relationships, i.e. sorting the top row and moving the data in successive rows accordingly.

This obviously is akin to the column sort, which sorts by one or more key columns, maintaining the relationship of the data. "

You've missed the point of what I wanted, Excel's SORT function Sorts the column (or row) you specify, AND moves the data in all the other columns or rows in sympathy, as in

1 3 2
6 5 4

becomes

1 2 3
6 4 5

see how row 2 has been modified because row 1 was sorted.

What I'm looking to do is

1 3 2
6 5 4

to become

1 2 3
4 5 6

  daba 12:02 19 Sep 2004

That didn't come out as intended - try

1 3 2 6 5 4

becomes

1 2 3

6 4 5

see how row 2 has been modified because row 1 was sorted.

What I'm looking to do is

1 3 2

6 5 4

to become

1 2 3

4 5 6

  pj123 12:30 19 Sep 2004

No, I don't think I have missed the point. I have just sorted 8 rows (albeit it one at a time) and they have come out exactly as I want (each row in ascending order). I run a Lottery Syndicate and have an excel sheet with all the numbers drawn from the very first game. I usually use the Newspapers or Camelot website to enter the draw numbers which are in ascending order into my spreadsheet in the first place. After all there are only 6 of them. I have written a Lottery Checker in excel which will check up to 84 lines. Two options here though. You could do the sort straight away after entering the numbers or, you could enter the numbers in ascendng order in the first place.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Xiaomi Mi Mix 2 review

What went wrong at the Designs of the Year 2017

iPhone X news: Release date, price, new features & specs

Comment regarder des séries et talk-shows américains en France ?