# Excel sort within ROWS



## fredman (Jul 13, 2002)

I have a spreadsheet with about 1,100 rows of data in it, and I want to sort each ROW (not column) in ascending order, from left to right, but only cells B C D E and F, the data in A, G and H must be left intact.
I can sort the entire XLS left to right, but that's not what I want, I just want those 5 cells within each row.

Is this easy, or am I missing something, or what ?


----------



## The Villan (Feb 20, 2006)

Select the data that you want to sort - (exclude field headings at the top of each column as well as columns A G & H)

Select the data menu and then click on sort
you will get a dialogue box appear
Click on the options button in the bottom left hand corner of the dialogue box
Select left to right under the Orientation option and click on OK
Make sure that the first row of your data appears in the first sort option
Select Ascending or Descending
Click on OK

That should do the trick

If you **** it up  just hold the Ctrl key on your keyboard and press the Z key on your keyboard (undo) and have another go.

Be careful about saving over the original file until you are really happy with the result


----------



## fredman (Jul 13, 2002)

Villan, it's just not working right. My first thought is I have a macro hidden somewhere screwing me up........

I followed what you said exactly, as I also followed M$ 'Help' on the same subject, I KNOW how to select the cells I want to sort (876 rows, columns b-c-d-e-f, sorted L to R ascending, I select 876, or 50, or even 10, it only sorts the first row of whatever I select, even though several rows under it are also selected.

I've done this before, but now it's not working, I'm trying to figure out what I am missing.....


----------



## The Villan (Feb 20, 2006)

Sent you a pm


----------



## bomb #21 (Jul 1, 2005)

fredman said:


> ... it only sorts the first row ... I've done this before, but now it's not working ...


Looked this one up ; the first relevant-looking response I found was from an MVP & provided a macro, which suggests that it can't be done with the "standard" method.

However, the macro didn't work confused: ), so I did a quick'n'dirty one. To use, just select the cells in column B & run. All it does is sort each 1 * 5 block in turn.

NB: make sure you save your file before running it. If you need help installing macros, post back.

Sub SortRows()
Application.ScreenUpdating = False
For Each Cell In Selection
Cell.Resize(1, 5).Sort Key1:=Cell, Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight
Next Cell
Application.ScreenUpdating = True
End Sub


----------



## bomb #21 (Jul 1, 2005)

The Villan said:


> Sent you a pm


Hopefully it wasn't like the PM you sent me (cropped for relevance).

"OK Bomb you seem to enjoy trying to bomb my posts. Because of this, I am going to stop helping anybody. It is not appreciated."

What "it" is is not clear.

"You are constantly posting macros as a solution, without knowing how much these people know about the application."

I specifically stated 'If you need help installing macros, post back'.

"You will notice that I am not so rude as to intervene in threads that you start helping on, because if somebody is already helping, it doesn't need 2 people trying to be smart arses with each other."

Intervene?!? You posted "Sent you a pm", which gives no-one any clue as to whether it's ongoing or solved or whatever, so how's that intervening? A PM in those circumstances undermines the principle of sharing info & solutions. From some of your earlier posts I got the impression that you can be a tad condescending & that you have a tendency to take umbrage a little too lightly. FTR I have no intention of trying to "bomb" your posts. If you have a problem with my "attitude", may I respectfully suggest that you put something appropriate in your sig (e.g. "bomb is a tosser") and continue helping posters here, rather than take it out on them.

</rant>

Edit: NB: written & posted while the post lambasting me was being deleted.


----------



## Ivan F Moala (Dec 26, 2004)

Try


```
Sub Test()
Dim RgToSort As Range
Dim RgRow As Range
Dim x As Long

Set RgToSort = Range(Range("B1:F1"), Range("B1:F1").End(xlDown))

For Each RgRow In RgToSort.Rows
    RgRow.Sort Key1:=Range(RgRow.Item(1).Address), Order1:=xlAscending, Orientation:=xlLeftToRight, OrderCustom:=1
Next

End Sub
```


----------



## The Villan (Feb 20, 2006)

Fred
Having seen your spreadsheet, I realised what you were trying to do and 
realised why you are having the problem.

I am explaining below what happens when you sort, as this helps you realise why you can't get the result you want.

Basically when you sort by row you can only have one main column that it 
will sort on. e.g.

1 3
5 1
3 2

if sorted by first column will show following result

1 3
3 2
5 1

the data in column 2 is not sorted it goes with the row it was associated 
with i.e. 3 2 stayed together, like wise 5 1 stayed together

When you sort left to right you have to realise that all data in column B 
stays in the same column, as does C, D, E etc

So when you sort left to right the first row will look sorted, but the rest 
of the rows don't seem to be sorted. e.g if we try to sort the following 
numbers left to right

3 1
1 5
2 3

it will result in the following

1 3
5 1
3 2

Thats becuase the second column in its entirety was sorted in front of the 
first column.

That is why it would seem as though the remaining rows weren't sorted.

OK so you can sort numbers left to right but only one row at a time.

Either Ivan F Maola's or Bombs solution look to be the macro you need, if 
you dont want to sort manually one row at a time. The macro will sort your 
numbers one row at a time until it has completed each row.

Unfortunately, I don't know how much you know about Macros. If you know a 
lot, and based on my explanation of sorting above, you will realise why you 
want to use the macro.

However if you don't use macros, thats another matter. In which case my 
simple explanation as to why you would want to use macros is that it simply 
takes the drudge work out of doing the job manually. In your case sorting 
800 odd or more blocks of data left to right - one row at a time, is a very 
time consuming issue and doing the job manually may mean mistakes occuring. 
The macro will do that in seconds depending on how fast your computer 
processor can work. So in the end you can save yourself one hell of a lot of 
time, plus you are less likely to make mistakes if your task is automated.

As bomb suggested, you can bounce off him, if you need to know how to setup 
either of the Macro's

I hope this has helped in the sense of explaining how sorting really works 
and why its not capable of doing exactly what you want in one go. Thats why 
macros exist 
Good luck with either of the the macro's.


----------



## fredman (Jul 13, 2002)

Ok, let's settle down a bit here..........

I really appreciate the help, I will bang on the macros tonight and report back.

In the meantime, PLEASE let me say: if a person (such as myself) posts a question/problem, it's TOTALLY COMMON for several people to interpret it differently, let alone have a totally different solution for it: programming (macros) is not done the same way by all people, so nobody should be pi$$ed off if others have (different) solutions, let alone be slightest bit offended, we can ALL learn from each other..........

'Can't we all just get along'


----------



## Zack Barresse (Jul 25, 2004)

bomb #21 said:


> Hopefully it wasn't like the PM you sent me (cropped for relevance).
> 
> "OK Bomb you seem to enjoy trying to bomb my posts. Because of this, I am going to stop helping anybody. It is not appreciated."
> 
> ...


Please keep all relevant private conversations to pm's and not on the open board. :up:


----------



## bomb #21 (Jul 1, 2005)

firefytr said:


> Please keep all relevant private conversations to pm's and not on the open board. :up:


You _are_ kidding me, right?

1 - there *was* no private conversation.

2 - you _do_ realise that one possible interpretation of your request is, "if you're being harassed, we don't wanna hear about it", right?

3 - my post did have a general purpose, namely to make posters aware they should perhaps tread carefully when "joining" villan in a thread.

With respect Zack, you're not a moderator here. I don't believe paraphrasing a PM to me breaches the rules. Even if you disagree, telling me what you think I should(n't) do *within a thread* isn't the way to go. :down:


----------



## Zack Barresse (Jul 25, 2004)

Bomb, please take these comments as just that, comments.

I moderate a LOT of other Office-related forums. No, I'm not a Moderator here, else I would have done things MUCH differently, probably including the deletion of some posts.

The fact of the matter is, regular board users do not want or need to see any personal communique from anybody on this board. I would say the same thing if Villan had posted what you did. I'm not saying you're right or he's right. That is between you two and the Moderators of this board.

So I guess to directly answer your questions:
1 - I have no idea what you did or didn't talk about
2 - And one possible interpretation of your comments are .. not so good. Good thing we're not assuming
3 - That is NOT YOUR RIGHT to point that out to users. You have every right to disagree and not like somebody, but not to infringe on others rights to do - or not do - the same thing, and frankly I don't want to hear it.



> With respect Zack, you're not a moderator here. I don't believe paraphrasing a PM to me breaches the rules. Even if you disagree, telling me what you think I should(n't) do within a thread isn't the way to go.


I'm not offended by this, it's completely your judgement here, that's cool. No, I'm not a Moderator and I don't believe you broke the rules; I was in no way trying to say that. But by your reasoning (of me not telling you what I think you should do in a thread) then you can say anything you want and I should keep my mouth shut? I think not. I have always enjoyed both yours and Villan's company, I just don't want - not appreciate - the buddings of a flame war on the open board. I've seen this (and dealt with it) far too often.

My apologies if I stepped on anybody's toes here.


----------



## bomb #21 (Jul 1, 2005)

While I can take them as comments I'm unable to leave them. 

"regular board users do not want or need to see any personal communique from anybody on this board" is a matter of opinion, *not* fact. I maintain that posting the abridged PM was for the benefit of other would-be helpers, i.e. so that they might know what they _might_ be letting themselves in for. Let's say _you_ were going to do something that unbeknown to you might come back on you -- if I _knew_ it might, then I'd be *obliged* to point that out, no?

"And one possible interpretation of your comments are .. not so good" is completely over my head. Please enlighten me so I can disabuse you (PM if you like  ).

"infringe on others rights to do - or not do - the same thing" -- AFAIK, I've not infringed on others' rights to do - or not do - *any*thing ; let alone *the same* thing, whatever that might be.

But my main problem is with "by your reasoning (of me not telling you what I think you should do in a thread) then you can say anything you want and I should keep my mouth shut? I think not", since it's a major misrepresentation. If you can point out where I said you should keep schtum, please do so. While it _seems_ unlikely that you didn't realise I was saying that "if one has a problem with a post, then one should report it", I guess that it _is_ possible ... inasmuchas most things are.

Rgds,
Andy


----------



## Zack Barresse (Jul 25, 2004)

I hate doing these in the open board and hijacking threads. If fredman permits, I'll make one more comment here as I feel it would be for everyones benefit.



bomb #21 said:


> "regular board users do not want or need to see any personal communique from anybody on this board" is a matter of opinion, *not* fact.


That is just my personal observation from the many boards and posts I have made. It's general netiquette when dealing with forums. This is only what I have surmised from the many boards/posts which I've made over the last few years; it may not pertain to everybody, only the majority of those which I have dealt with personally.



bomb #21 said:


> I maintain that posting the abridged PM was for the benefit of other would-be helpers, i.e. so that they might know what they _might_ be letting themselves in for. Let's say _you_ were going to do something that unbeknown to you might come back on you -- if I _knew_ it might, then I'd be *obliged* to point that out, no?


I think this is the largest difference of our opinions. (Shall we agree to disagree here?  ) I've been in situations on other boards where another person who is completely ignorant and self-righteous posts flaming comments either in pm or on the board; plus their advice is not the greatest (IMHO). If we were to post comments to the same thread, which happens sometimes as we're both avid posters on a certain unmentioned board, the course of action for me would not be to openly post that I think the other person might behave badly or warn other users about any potential side effect from this other user. When asked in pm, I give my full unbridled opinion. 



bomb #21 said:


> "And one possible interpretation of your comments are .. not so good" is completely over my head. Please enlighten me so I can disabuse you (PM if you like  ).


I was just trying to convey here that your comments may be mistrued because other readers are getting a glance at something which they may not necessarily know all aspects of; hence better to keep everything to pm. Plus the board being used for archive purposes and searchable solutions, it kinda stinks when these type of conversations pop up.



bomb #21 said:


> "infringe on others rights to do - or not do - the same thing" -- AFAIK, I've not infringed on the others' rights to do - or not do - *any*thing ; let alone *the same* thing, whatever that might be.


By posting these comments to the open board, you - and I - have taken away some of the rights to other readers here. We are subjecting them to thoughts and messages which they might not want to be privy to. Others do not get a choice when these conversations are *public*.



bomb #21 said:


> But my main problem is with "by your reasoning (of me not telling you what I think you should do in a thread) then you can say anything you want and I should keep my mouth shut? I think not", since it's a major misrepresentation. If you can point out where I said you should keep schtum, please do so. While it _seems_ unlikely that you didn't realise I was saying that "if one has a problem with a post, then one should report it", I guess that it _is_ possible ... inasmuchas most things are.


I probably did just misinterpret what you said. It came off like a double standard.

Let me give a slight synopsis of my thoughts here:

Everyone is entitled to their opinion
Our opinions are not necessarily *ok* to exprerss on the open board
Respecting other peoples space/rights should be of utmost importance
Any flaming or construed flaming should be kept in private
All derogatory comments should be kept in private
All such comments should be reported
A new thread should be opened for diverse conversations not pertaining to the thread topic
Public warning of others can be constued as flaming
Opinions of others should be kept to ones self
PMs are perfectly ok for giving your opinion of others 

If any of this is interpreted by any reader/user as a disregard for the rules, I think the only one it comes close to would be a Category III Offense: Crude or Rude Intent. I don't think it has violated the rules but I am not a deciding voice on this board. It appears to still fall within the governing rules of this board as I see this as quite productive (hence the public post) as believe we can all learn from this.

I hope I have not offended anybody in this posting. Take care!


----------



## bomb #21 (Jul 1, 2005)

Perfectly happy to agree to disagree on the (agreed) largest difference of our opinions. 

The rest I'll have to chew over when time permits. For now, coding beckons. 

Rgds,
Andy


----------



## fredman (Jul 13, 2002)

I almost apologize I asked anything.

Bomb, don't chew on anything, life is too short, let it go and just move on.

Can we just drop it everybody ? We ALL have too many other GOOD things to do........ like I have to go remember how to record a macro - I haven't done that in probably 6 years, it'll come back to me........


----------



## bomb #21 (Jul 1, 2005)

Tools -- Macro -- Record New Macro. & don't apologise.


----------



## bomb #21 (Jul 1, 2005)

fredman said:


> Bomb, don't chew on anything, life is too short, let it go and just move on.


Appreciate your concern there, fredman. However, chewing it over is pretty much bomb #21's _raison d'etre_ so it's no biggie. 

So, Zack, I checked the rules preamble, and specifically noted:

" ... our goal is to provide useful technical support in a congenial atmosphere, which is the ultimate guideline we will use when evaluating any post or thread, *or the conduct of any member*."

Thus it could be argued that sending not particularly congenial PMs constitutes conduct with potential to undermine the primary goal, thus receipt of such PMs could be reported. However, (a) I doubt the adminstration would act on such reports (no disrepect intended -- workload'n'all) (b) reporting isn't really my thing (c) there'd be an element of locking the stable door after the horse had bolted.

In short, & re: "regular board users do not want or need to see any personal communique from anybody on this board", as a regular board user -- if someone wants to give a heads up a la "if you do so & so, you might get a flea in your ear", I'd personally welcome it. & I'm not just being contrary, the main principle is a major thing for me. I had to do "something" about "something" in June last year ; if someone had said to me then, "OK, do what your conscience dictates, just be aware you could be setting yourself up for 8-9 months of stressed out-ness from semi-legal crap", I'd have known what to expect & given him or her my undying gratitude.

Here endeth my contribution to this aspect of t'thread.


----------



## bomb #21 (Jul 1, 2005)

fredman said:


> I have to go remember how to record a macro - I haven't done that in probably 6 years, it'll come back to me........


Just to clarify, "Tools -- Macro -- Record New Macro". You should see a dialog like the attached. Make sure "Store in" is set to "This Worbook", then click OK.

Next, do nothing other than click the "Stop Recording" button on the "Stop Recording" toolbar that _should_ appear.

Now press ALT+F8 to show the "Macro" dialog. Unless you renamed it, "Macro1" should show up in the macros listbox. Select it, then click Edit. This'll take you to the VBA module storing the code. Since you did nothing while recording, it'll only be "bumph", e.g.:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 28/03/2006 by Andy Brown
'

'
End Sub

So you can delete all of that and paste in an example you've been given (I'd suggest Ivan's since he's a proper coder, with the declaring variables'n'all  ). Then press ALT+Q to close the VBE & return to Excel. All that remains is to run the code via the "Macro" dialog.

Rgds,
Andy


----------



## fredman (Jul 13, 2002)

I finally got to it the other night - yep, it's just like the Word Macros I used to do about a million years ago, and it works GREAT, I tried both and they are fine, I appreciate it.

Now for the next project - searching these sorted rows - just the 5 columns I am sorting.....
'Regular' Windows search or find does not work.


----------

