Sorting each individual row of numbers in ascending order

[hedley]

Helo all, Hedley here,


I have many times tried to sort many rows all at once in excel without any success.

I seem to be able to sort only one row at a time, then select the next row and use cntrl 'y' to repeat the sort. I have 1000 rows of data to sort.

I have included a sample sheet accessible from Google Docs, with the top 5 rows being the unsorted data, and the bottom 5 rows being the desired outcome.


Please help !!


https://docs.google.com/open?id=0Bw_GUXWdn3mgN2hVOUhTbDZadFE

 

[bobhc]

Good evening hedley


I select all of your data then I clicked sort/smallest to largest and all was sorted, no formulas, no VBA

You say that the bottom five are the desired out come but in what way are they sorted?

 

[hedley]

hi bobhc ,


The bottom 5 rows are sorted left to right in numerical order, the top five rows are jumbled. Each individual row needs to be sorted in numerical order.


I go data, sort, then it asks me to sort by a row number and by doing that it is all jumbled up.


I need each and every row sorted from lowest number to highest number.


e.g. there are 18 numbers on each row, if the numbers on a row were 1-18 all jumbled up, they need to end up :


1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18


Now they are all sorted into numerical order.


hedley

 

[Debraj]

Hi, Hedley!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the three first green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about your question...


I think you need to go for VBA:

Open VBA Editor (Alt + F11)

Insert a New Module

and paste the below Code there...

[pre]

Sub SortLotOfRow()
Application.ScreenUpdating = False
For I = 2 To ActiveSheet.UsedRange.Rows.Count
Rows(I).Select
ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add Key:=Rows(I), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveSheet.Sort
.SetRange Rows(I)
.Header = xlYes
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With
Next I
Application.ScreenUpdating = True
Range("a1").Select
End Sub

[/pre]
Please Run the Macro (F5) and Confirm if the result is as per your requirement or not,

Regards,

Deb

 

[hedley]

Hi Debraj,


Thanks for your advice and the code.


When i tell it to run the macro it gives an error on the following line :


ActiveSheet.Sort.SortFields.Clear


I have never used VBA before, but managed to copy and paste easy enough, and then ran it and the error occoured.


Before I ran it, I selected my data to be sorted, I assume that this was correct ?


Hedley

 

[Debraj]

Hi Hedley,


Can you please change whole coding to the below, and try again..

[pre]

Sub SortLotOfRow()
Application.ScreenUpdating = False
X = ActiveSheet.UsedRange.Rows.Count
CL = ActiveSheet.UsedRange.Columns.Count
For RW = 2 To X
For i = 2 To CL - 1
For j = i + 1 To CL
If Cells(RW, i) > Cells(RW, j) Then
Temp = Cells(RW, j).Value
Cells(RW, j) = Cells(RW, i)
Cells(RW, i) = Temp
End If
Next j
Next i
Next RW
Application.ScreenUpdating = True
End Sub

[/pre]

Regards,

Deb

 

[hedley]

Hi Debraj,


OK, changed to new code.


What are the exact steps to run it ?


thanks


Hedley

 

[Debraj]

Hi Hedley,

* Open Excel,

* Go to VBA Editor (Alt + F11)

* Insert New Module (Insert > Module)

* paste the Mentioned Code. (if top of the line is

Option Explicit

then delete it)

* Now Run the Code (F5)


Voila, your sheet is now in sort order.

For detail, download the attached file.

https://dl.dropbox.com/u/78831150/Excel/SortLotOfRow.xlsm


Regards,

Deb

 

[SirJB7]

@Debraj Roy

Hi!

That code (for i=1 to N-1, for j=i+1 to N...) brought to my mind older times, when sorting lists was made manually by code...

Yesterday I wrote something about Donald Knuth and his Algorithms + Data Structures = Programs, and that's were I first saw this technique. Fond memories

Regards!

 

[hedley]

Hi Debraj,

Absolutely wonderful !!!

I tested your example worksheet first, got that to work, went back to mine, still had trouble.

Then I went and deleted the module in mine, copied and pasted the new code again and hey presto, it worked wonderfully.

This will save me untold amounts of time in many of the projects that I am working on.

Thank you very,very,very much


Regards,

Hedley

 

[Guest]

 

[Guest]

jordans shoes,abercrombie outlet, Related articles: http://librarylab. yeah. in/indexto help him make mouth to mouth resuscitation ssdpedia. michael kors outlet,abercrombie france,Related articles: http://quan.law.
BA. this is well understood. if I continue to try,sac lancel pas cher,air jordans,air jordans shoes, com/forum. Abercrombie,http://b-b-u.com/events_view.php?eid=13209,michael kors outlet, sooner or later As a result. Unable to move. So that night cappella stop leaving only a faint silhouette whether my melancholy with the night still and quiet if recalled to stop the spread leaving only the light the warmth whether my thoughts with memory terminate to stop When Love past memory disappointment time . SFN China essay after have to recognize the poor recognized by the Government.
pontetec.outwar.Abercrombie,louboutin, I don't agree,air jordan,http://bestringtonesfree.com/whitney-houston-its-not-right-but-its-okay-thunderpuss-2000-club-mix-dj-be-edit/, I think. if I continue to try,air jordan pas cher,com/profile.ralph lauren pas cher,sac lancel,http://www.crime-wiki.org/index.php?title=User:Fkgfzl4ca8#Then_sliced_the_ham, away with the level of text to write this story.89.
ralph lauren,abercrombie,longchamp,jordan, camellia fragrance overflow terraces .