Sorting Rows Doesn't Work, Please Help

[binar]

Fellow Forum Members,
I have like 1400 rows of lotto winning history (small sample below). My
objective is to sort in ascending order each row. The problem is that
Excel 2003 is behaving oddly. I'm using Data-> Sort -> Options -> Sort
Left to Right command. And I have highlighted the data in several ways
with no success. Excel only sorts one row even though I have 1400 rows
selected. Can someone help me with a macro that will sort the numbers
row by row in ascending order? Any help will be greatly appreciated.
Thanks.

37 36 26 42 10 23
2 48 3 1 13 28
13 28 4 46 3 35
11 6 31 2 34 14
27 17 13 42 47 40
4 38 42 23 3 10
48 15 44 20 13 17
3 19 7 5 13 20
36 12 19 13 20 48
15 40 8 47 25 22
18 15 23 19 11 22
30 44 17 49 42 15

 

[Dave Peterson]

You'll have to do it one row at at time...

Or use a macro to do it for you.

If you want to try a macro:

Option Explicit
Sub testme()

Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long

With Worksheets("sheet1")
FirstRow = 2
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = FirstRow To LastRow
With .Cells(iRow, "A").Resize(1, 6)
.Sort Key1:=.Columns(1), Order1:=xlAscending, _
Header:=xlNo, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlLeftToRight
End With
Next iRow
End With
End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[binar]

Dave,
You are the man! Thanks for the help. I never in a thousand years could
have done this on my own because the programming looks complex. It's a
shame that Microsoft doesn't upgrade Excel so that these kinds of
operations are already included in the application. It doesn't make
sense why such a critical operation would be left out. Looking at the
code I noticed that it states the following: FirstRow = 2. I'm going to
change it to 1, because this row is not being sorted. Lastly, thanks for
the link to writing macros. It seems to be a valuable resource for
newbies.
Cheers,
Binar

 

[Dave Peterson]

Glad you got it working.

I guessed (incorrectly) that you may have headers in row 1 that shouldn't be
sorted.

 

[dbahooker]

keep your DATA in a DATABASE

in Access you can RIGHT-CLICK 'SORT' and RIGHT-CLICK 'FILTER' on
anything that you want.

-Aaron

 

[Harlan Grove]

(e-mail address removed) wrote...

keep your DATA in a DATABASE

in Access you can RIGHT-CLICK 'SORT' and RIGHT-CLICK 'FILTER' on
anything that you want.

....

Really? How does one sort fieds in each record separately in any
database? That is, given the OP's sample data as a table,

F1____F2____F3____F4____F5____F6
37____36____26____42____10____23
_2____48_____3_____1____13____28
13____28_____4____46_____3____35
11_____6____31_____2____34____14
27____17____13____42____47____40
_4____38____42____23_____3____10
48____15____44____20____13____17
_3____19_____7_____5____13____20
36____12____19____13____20____48
15____40_____8____47____25____22
18____15____23____19____11____22
30____44____17____49____42____15

show us a query that could transform this into

F1____F2____F3____F4____F5____F6
10____23____26____36____37____42
_1_____2_____3____13____28____48
_3_____4____13____28____35____46
_2_____6____11____14____31____34
13____17____27____40____42____47
_3_____4____10____23____38____42
13____15____17____20____44____48
_3_____5_____7____13____19____20
12____13____19____20____36____48
_8____15____22____25____40____47
11____15____18____19____22____23
15____17____30____42____44____49

It can be done in Excel using formulas rather than macros. If the
topmost record were in A2:F2, then the array formula

=SMALL(A2:F2,{1,2,3,4,5,6})

in H2:M2 would sort the fields in the record in ascending order. Fill
down as needed.

So put me to shame and dazzle us all with the wealth of your knowledge
and experience by showing a query that does this. It should be really
simple for someone who CLAIMS to know as much as you do.

 

[greaseman]

Hey, Aaron you dimwit....

This is an Excel forum...... why do you keep providing MS Access
solutions???? I suppose in Access forums you are providing Excel
solutions!

Geeeezzz..... you must be fun to work with (that is, if you have found
a job after beig fired from Microsoft).... I can see you in meetings:
Boss: "Our company owns 10 copies of Excel. We need a programmed
solution for our Accounting department, written in Excel." Aaron: "I
can give you anything you want in minus three seconds, because I'm so
wonderful. It will be in Access because I dont know anything else."

You'd probably lose your head if it wasn't screwed so tightly up your
butt! Like I said....THIS IS AN EXCEL FORUM!! THE LEAST YOU CAN DO IS
GIVE YOUR PIN-HEADED ATTEMPTS IN EXCEL!!

 

[Dave Peterson]

Why are you posting in this thread?

And why are you replying to me? I'm not Aaron.

 

[greaseman]

I believe I was replying to post # 5 in this thread, the one written by
dbahooker (aka Aaron). Since I started my reply with "Hey Aaron,..."
what made you think I was replying to what you wrote?

 

[Jay Petrulis]

Hey, Aaron you dimwit....

This is an Excel forum...... why do you keep providing MS Access
solutions???? I suppose in Access forums you are providing Excel
solutions!

Your post is spot on, except for this part. When has Aaron ever
provided a "solution" to any question? All he does is spout some
database propoganda without ever giving any help.

I don't think he can give a solution in either Access or Excel.

 

[Dave Peterson]

I don't see any messages from Aaron in this thread when I connect directly to
the MSNewsgroups.

But I do see both Aaron's and Harlan's when I search via google.

Maybe it's because they both posted via Google???

Sorry about the (my) confusion.

 

[Harlan Grove]

Dave Peterson wrote...

I don't see any messages from Aaron in this thread when I connect directly to
the MSNewsgroups.

....

Newsgroups hosted on msnews.microsoft.com are filtering out Aaron's
postings and some responses to him. But then again, Microsoft's news
servers seem to miss a lot of postings originated from Google Groups
and ExcelForum, and Google Groups misses a lot of posting originated
from Microsoft's news servers.

 

[Dave Peterson]

Not altogether a bad thing <bg>.

Dave Peterson wrote...
...

Newsgroups hosted on msnews.microsoft.com are filtering out Aaron's
postings and some responses to him. But then again, Microsoft's news
servers seem to miss a lot of postings originated from Google Groups
and ExcelForum, and Google Groups misses a lot of posting originated
from Microsoft's news servers.

 

[greaseman]

Jay,

You are absolutely correct..... Aaron has never offered solutions to
anything anywhere. I pity the company that he works for, if there is
one.

Dave,

Perfectly understandable...... some of these newsgroups / forums can
be a bit confusing......

Harlan,

How might I go about determining where a response in this forum
originated from?

 

[Dave Peterson]

I'm not Harlan, but I looked via google:

http://groups.google.co.uk/group/mi...afd8e?lnk=st&q=&rnum=1&hl=en#ceed678911fafd8e

or
http://snipurl.com/ulwd

Then I looked at the message in the thread that I cared about.
I clicked on "Show Options"
then I clicked on "Show Original"

I saw this for your last message:

From: greaseman <[email protected]>
Subject: Re: Sorting Rows Doesn't Work, Please Help
Date: Tue, 8 Aug 2006 13:03:53 -0400
Message-ID: <[email protected]>
Organization: ExcelTip
User-Agent: ExcelTipForum
X-Newsreader: ExcelTipForum


For one of Harlan's messages:

Path:
g2news2.google.com!postnews.google.com!75g2000cwc.googlegroups.com!not-for-mail
From: "Harlan Grove" <[email protected]>
Newsgroups: microsoft.public.excel
Subject: Re: Sorting Rows Doesn't Work, Please Help
Date: 7 Aug 2006 20:21:27 -0700
Organization: http://groups.google.com


If you're using a newsreader, you can usually click on some option to show
headers.

 

[Harlan Grove]

greaseman wrote...
....

How might I go about determining where a response in this forum
originated from?

All newsgroup postings have a header that includes message tags that
identify the message itself, the chain of previous messages when the
message in question is a response, the sender's name, the newsgroup(s)
to which it's sent, the message subject and various other tags. The
header is terminated with a blank line, and the body of the message
follows after that.

The key tag is Message-ID. That tag in your message, to which this is a
response, looks like

Message-ID: <[email protected]>

which tells me you're posting via ExcelForum. that being the case, it's
unlikely you can see newsgroup message headers, so you may not be able
to determine where any message originates. The Microsoft web portal is
similarly limited. Google Groups, OTOH, does provide an option to
display full messages including headers. All NNTP newsreaders, such as
Outlook Express, Xnews etc., also provide ways to view message headers.

 

[greaseman]

Dave and Harlan,

Thanks for your replies about newsgroups. I appreciate the
information.

 

[aaron.kempf]

he would have it properly normalized if it was in a database.

sorting woudl be as easy as right-click SORT.

Excel is for babies and it isn't a match for Access.

RIGHT-CLICK 'SORT' and RIGHT-CLICK 'FILTER'

How many Excel macros does it take to make you kids HALF as powerful as
I am??

-Aaron
ADP Nationalist

 

[aaron.kempf]

yeah MS is a bunch of drunk CORNHOLERS that **CENSOR** what I say.

it's bullshit.

-Aaron

 

[aaron.kempf]

well Jay

I don't think that you have a PENIS.

I don't need a solution.

RIGHT CLICK SORT ASSHOLE.

It's in Access.. but Excel can't do this??


ROFL