Sorting Rows Doesn't Work, Please Help

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

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 wrote:
>
> 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
>
> --
> binar
> ------------------------------------------------------------------------
> binar's Profile: http://www.excelforum.com/member.php...o&userid=37140
> View this thread: http://www.excelforum.com/showthread...hreadid=568673

--

Dave Peterson

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

Originally Posted by 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 wrote:
>
> 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
>
> --
> binar
> ------------------------------------------------------------------------
> binar's Profile: http://www.excelforum.com/member.php...o&userid=37140
> View this thread: http://www.excelforum.com/showthread...hreadid=568673

--

Dave Peterson

Glad you got it working.

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

binar wrote:
>
> 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 Wrote:
> > 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 wrote:
> > >
> > > 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
> > >
> > > --
> > > binar
> > >
> > ------------------------------------------------------------------------
> > > binar's Profile:
> > http://www.excelforum.com/member.php...o&userid=37140
> > > View this thread:
> > http://www.excelforum.com/showthread...hreadid=568673
> >
> > --
> >
> > Dave Peterson
>
> --
> binar
> ------------------------------------------------------------------------
> binar's Profile: http://www.excelforum.com/member.php...o&userid=37140
> View this thread: http://www.excelforum.com/showthread...hreadid=568673

--

Dave Peterson

keep your DATA in a DATABASE

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

-Aaron



Dave Peterson wrote:
> Glad you got it working.
>
> I guessed (incorrectly) that you may have headers in row 1 that shouldn't be
> sorted.
>
> binar wrote:
> >
> > 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 Wrote:
> > > 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 wrote:
> > > >
> > > > 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
> > > >
> > > > --
> > > > binar
> > > >
> > > ------------------------------------------------------------------------
> > > > binar's Profile:
> > > http://www.excelforum.com/member.php...o&userid=37140
> > > > View this thread:
> > > http://www.excelforum.com/showthread...hreadid=568673
> > >
> > > --
> > >
> > > Dave Peterson
> >
> > --
> > binar
> > ------------------------------------------------------------------------
> > binar's Profile: http://www.excelforum.com/member.php...o&userid=37140
> > View this thread: http://www.excelforum.com/showthread...hreadid=568673
>
> --
>
> Dave Peterson

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!!

Why are you posting in this thread?

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

greaseman wrote:
>
> 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!!
>
> --
> greaseman
> ------------------------------------------------------------------------
> greaseman's Profile: http://www.excelforum.com/member.php...o&userid=28808
> View this thread: http://www.excelforum.com/showthread...hreadid=568673

--

Dave Peterson

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?

greaseman wrote:
> 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.

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.


greaseman wrote:
>
> 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?
>
> --
> greaseman
> ------------------------------------------------------------------------
> greaseman's Profile: http://www.excelforum.com/member.php...o&userid=28808
> View this thread: http://www.excelforum.com/showthread...hreadid=568673

--

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.


greaseman wrote:
>
> 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?
>
> --
> greaseman
> ------------------------------------------------------------------------
> greaseman's Profile: http://www.excelforum.com/member.php...o&userid=28808
> View this thread: http://www.excelforum.com/showthread...hreadid=568673

--

Dave Peterson

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.

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?

Not altogether a bad thing <bg>.

Harlan Grove wrote:
>
> 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

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

http://groups.google.co.uk/group/mic...ed678911fafd8e

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.

greaseman wrote:
>
> 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?
>
> --
> greaseman
> ------------------------------------------------------------------------
> greaseman's Profile: http://www.excelforum.com/member.php...o&userid=28808
> View this thread: http://www.excelforum.com/showthread...hreadid=568673

--

Dave Peterson

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.

Dave and Harlan,

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

Harlan Grove wrote...
....
>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
....
>
>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
....

So, Aaron, I take it by your silence on this specific question that you
have NO CLUE how to do this with ANY sort of query. Guess this just
goes to show that one thing databases don't handle easily is swapping
values between fields in the same record.

There may be a way to do this with databases. First you'd need to
convert the first table above to something like

OrigRecNum__ColNum__Value
_____1__________1_____37
_____1__________2_____36
_____1__________3_____26
_____1__________4_____42
_____1__________5_____10
_____1__________6_____23
_____2__________1______2
_____2__________2_____48
_____2__________3______3
_____2__________4______1
_____2__________5_____12
_____2__________6_____28

Then sort this new table by OrigRecNum then Value, both in ascending
order to get

OrigRecNum__ColNum__Value
_____1__________5_____10
_____1__________6_____23
_____1__________3_____26
_____1__________2_____36
_____1__________1_____37
_____1__________4_____42
_____2__________4______1
_____2__________1______2
_____2__________3______3
_____2__________5_____12
_____2__________6_____28
_____2__________2_____48

Then update the ColNum field to show the count of records with the same
OrigRecNum and Value <= the current record's Value. The result would
look like

OrigRecNum__ColNum__Value
_____1__________1_____10
_____1__________2_____23
_____1__________3_____26
_____1__________4_____36
_____1__________5_____37
_____1__________6_____42
_____2__________1______1
_____2__________2______2
_____2__________3______3
_____2__________4_____12
_____2__________5_____28
_____2__________6_____48

And finally put this back into crosstab layout.

F1____F2____F3____F4____F5____F6
10____23____26____36____37____42
_1_____2_____3____13____28____48

Does this help you? Why don't you show us what the queries would look
like? Dazzle us with how 4 nontrivial queries would be oh, so much more
efficient than the Excel equivalent of a single array formula filled
down (one moderately somplicated formula followed by one simple
operation).

you keep your data properly normalized.

having columns named F1, F2, F3, F4, F5, F6 isn't acceptable to ANY
real developer.

it's time that u excel dorks learn some real tools.. and it's time that
companies start enforcing real SDLC 'best practices' inside of Excel.

2/3rds of companies paychecks go to idiots that spend all day copying
and pasting.

You CON-ARTISTS can lick my nuts.

Do something productive. Learn Crystal Reports.

-Aaron


Harlan Grove wrote:
> Harlan Grove wrote...
> ...
> >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
> ...
> >
> >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
> ...
>
> So, Aaron, I take it by your silence on this specific question that you
> have NO CLUE how to do this with ANY sort of query. Guess this just
> goes to show that one thing databases don't handle easily is swapping
> values between fields in the same record.
>
> There may be a way to do this with databases. First you'd need to
> convert the first table above to something like
>
> OrigRecNum__ColNum__Value
> _____1__________1_____37
> _____1__________2_____36
> _____1__________3_____26
> _____1__________4_____42
> _____1__________5_____10
> _____1__________6_____23
> _____2__________1______2
> _____2__________2_____48
> _____2__________3______3
> _____2__________4______1
> _____2__________5_____12
> _____2__________6_____28
>
> Then sort this new table by OrigRecNum then Value, both in ascending
> order to get
>
> OrigRecNum__ColNum__Value
> _____1__________5_____10
> _____1__________6_____23
> _____1__________3_____26
> _____1__________2_____36
> _____1__________1_____37
> _____1__________4_____42
> _____2__________4______1
> _____2__________1______2
> _____2__________3______3
> _____2__________5_____12
> _____2__________6_____28
> _____2__________2_____48
>
> Then update the ColNum field to show the count of records with the same
> OrigRecNum and Value <= the current record's Value. The result would
> look like
>
> OrigRecNum__ColNum__Value
> _____1__________1_____10
> _____1__________2_____23
> _____1__________3_____26
> _____1__________4_____36
> _____1__________5_____37
> _____1__________6_____42
> _____2__________1______1
> _____2__________2______2
> _____2__________3______3
> _____2__________4_____12
> _____2__________5_____28
> _____2__________6_____48
>
> And finally put this back into crosstab layout.
>
> F1____F2____F3____F4____F5____F6
> 10____23____26____36____37____42
> _1_____2_____3____13____28____48
>
> Does this help you? Why don't you show us what the queries would look
> like? Dazzle us with how 4 nontrivial queries would be oh, so much more
> efficient than the Excel equivalent of a single array formula filled
> down (one moderately somplicated formula followed by one simple
> operation).

inflexible and difficult to learn?

you WIMPS need to bite the bullet and learn some real skills.

It took me YEARS to be able to learn how to build a responsive
database.

but I stuck with it. I read books on it. I took classes on it in
college.

And I slaved and slaved until I could do it.

MDX is 10 times more powerful and complex than SQL. I don't mean SQL
Server; I mean all dialects of ANSI-SQL.

Yes. It is a FRIGGIN ***** to learn.

But Excel was too.

Excel is harder to use than any other product on the market.

Why is it popular?

Because you idiots DONT HAVE THE BALLS to learn a real program.

It's laughable.

What did Opperheimer say when FDR said 'build me a nuclear bomb'

did he shiver and say 'oh but its TOO HARD'

Did Michael Jordan stop practicing because it was 'TOO HARD'?

Grow some balls kids; you're all stuck in the first grade of the
technology ladder.

-Aaron




Harlan Grove wrote:
> [email protected] wrote...
> >THEN WHY DO YOU KIDS USE IT FOR ETL?
> ...
>
> Not me.
>
> Who?
>
> You?
>
> Some unnamed spreadsheet users?
>
> Spreadsheets are misused for lots of things. There've been newsgroup
> postings asking for help making Excel generate temporary usernames and
> passwords for students in courses that require computer work. That's a
> task for which there have been much better, more robust tools (shell
> scripts) available for decades.
>
> SOME people will continue to misuse spreadsheets for the forseeable
> future. Most of them will do so because they don't know anything else
> that could automate their tasks. A few will do so because spreadsheets
> are the only tool they have that could allow them to automate their
> tasks.
>
> If you want to show them IN DETAIL how their tasks would be easier in
> Access or some other database, go ahead and do so (for a change). Just
> telling that Access would be easier without showing them how isn't
> going to change anyone's mind. But you're not here to change any minds,
> are you? You're just here to rant.
>
> >etl isn't test processing.. it's mainly lookups and designing
> >components for ultra-high performance
> ...
>
> You mean TEXT processing? Since when are lookups calculations?
> Associative arrays, aka hashes, provided by all scripting languages are
> more efficient than spreadsheet lookup functions.
>
> Use the best tool for the task. Spreadsheets aren't ideal for ETL.
>
> >of course; it's a 100 times more efficient because you can write MDX
> >formulas and equations.. I mean..
> ...
>
> To repeat from months ago, there have been attempts to sell 'financial
> modeling packages' for years. Lotus Improv was the first. Then there
> was Advance. Now there's Quantrix. They don't sell in large volume
> because they're inflexible and difficult to learn. Same can be said of
> MDX. This doesn't mean they don't have their uses, but they're not for
> everyone. They're certainly not the panacea you think they are.

[email protected] wrote:
> blah, blah, blah
>
> -Aaron
>

F1...F6 represent variable field names. Call them whatever you'd like.
Harlan gave you an outline of a possible solution using a database
(far more than you can do). That you chose to interpret his post to
mean that he doesn't know how to give descriptive names says a lot
about your ability to work with databases.

Grapefruit juice.

well it technically should be ONE FIELD.

-Aaron


Jay Petrulis wrote:
> [email protected] wrote:
> > blah, blah, blah
> >
> > -Aaron
> >
>
> F1...F6 represent variable field names. Call them whatever you'd like.
> Harlan gave you an outline of a possible solution using a database
> (far more than you can do). That you chose to interpret his post to
> mean that he doesn't know how to give descriptive names says a lot
> about your ability to work with databases.
>
> Grapefruit juice.

Harlan;

you don't need the '' column because it doesnt' TELL YOU ANYTHING.

if you wanted to -- you could use the RANK function to tell what
ordinal position it SHOULD BE IN.

But your design is flawed because it has information that is
unnecessary.

We don't care about what ordinal position the numbers were pulled in..
right?
so why would we store it?

Note: this ranking function might take a different shape; I would
probably change it to a query or 2 instead of tmpTables...

Select OrigRecNum, (select count(distinct value) from TmpLotteryNumbers
subQ Where SubQ.Value < TmpLotteryNumbers.Value + 1) as Rank
Into TmpLotteryNumbers
From
(
Select OrigRecNum, F1 as Value
UNION
Select OrigRecNum, F2
UNION
Select OrigRecNum, F3
UNION
Select OrigRecNum, F4
UNION
Select OrigRecNum, F5
UNION
Select OrigRecNum, F6
) derived1

Select OrigRecNum,
(Select Value From TmpLotteryNumbers SubQ WHERE SubQ.OrigRecNum =
TmpLotteryNumbers.OrigRecNum AND Rank = 1) As F1,
(Select Value From TmpLotteryNumbers SubQ WHERE SubQ.OrigRecNum =
TmpLotteryNumbers.OrigRecNum AND Rank = 2) As F2,
(Select Value From TmpLotteryNumbers SubQ WHERE SubQ.OrigRecNum =
TmpLotteryNumbers.OrigRecNum AND Rank = 3) As F3,
(Select Value From TmpLotteryNumbers SubQ WHERE SubQ.OrigRecNum =
TmpLotteryNumbers.OrigRecNum AND Rank = 4) As F4,
(Select Value From TmpLotteryNumbers SubQ WHERE SubQ.OrigRecNum =
TmpLotteryNumbers.OrigRecNum AND Rank = 5) As F5,
(Select Value From TmpLotteryNumbers SubQ WHERE SubQ.OrigRecNum =
TmpLotteryNumbers.OrigRecNum AND Rank = 6) As F6
From
(Select Distinct OrigRecNum From TmpLotteryNumbers) derived1


Altogether this would take me less than 30 seconds.




Harlan Grove wrote:
> Harlan Grove wrote...
> ...
> >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
> ...
> >
> >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
> ...
>
> So, Aaron, I take it by your silence on this specific question that you
> have NO CLUE how to do this with ANY sort of query. Guess this just
> goes to show that one thing databases don't handle easily is swapping
> values between fields in the same record.
>
> There may be a way to do this with databases. First you'd need to
> convert the first table above to something like
>
> OrigRecNum__ColNum__Value
> _____1__________1_____37
> _____1__________2_____36
> _____1__________3_____26
> _____1__________4_____42
> _____1__________5_____10
> _____1__________6_____23
> _____2__________1______2
> _____2__________2_____48
> _____2__________3______3
> _____2__________4______1
> _____2__________5_____12
> _____2__________6_____28
>
> Then sort this new table by OrigRecNum then Value, both in ascending
> order to get
>
> OrigRecNum__ColNum__Value
> _____1__________5_____10
> _____1__________6_____23
> _____1__________3_____26
> _____1__________2_____36
> _____1__________1_____37
> _____1__________4_____42
> _____2__________4______1
> _____2__________1______2
> _____2__________3______3
> _____2__________5_____12
> _____2__________6_____28
> _____2__________2_____48
>
> Then update the ColNum field to show the count of records with the same
> OrigRecNum and Value <= the current record's Value. The result would
> look like
>
> OrigRecNum__ColNum__Value
> _____1__________1_____10
> _____1__________2_____23
> _____1__________3_____26
> _____1__________4_____36
> _____1__________5_____37
> _____1__________6_____42
> _____2__________1______1
> _____2__________2______2
> _____2__________3______3
> _____2__________4_____12
> _____2__________5_____28
> _____2__________6_____48
>
> And finally put this back into crosstab layout.
>
> F1____F2____F3____F4____F5____F6
> 10____23____26____36____37____42
> _1_____2_____3____13____28____48
>
> Does this help you? Why don't you show us what the queries would look
> like? Dazzle us with how 4 nontrivial queries would be oh, so much more
> efficient than the Excel equivalent of a single array formula filled
> down (one moderately somplicated formula followed by one simple
> operation).

Binar;

it's a shame you don't learn how to use databases.

Excel isn't a tool for sorting.. it doesn't even support 'RIGHT-CLICK
SORT'.




binar wrote:
> 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 Wrote:
> > 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 wrote:
> > >
> > > 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
> > >
> > > --
> > > binar
> > >
> > ------------------------------------------------------------------------
> > > binar's Profile:
> > http://www.excelforum.com/member.php...o&userid=37140
> > > View this thread:
> > http://www.excelforum.com/showthread...hreadid=568673
> >
> > --
> >
> > Dave Peterson
>
>
> --
> binar
> ------------------------------------------------------------------------
> binar's Profile: http://www.excelforum.com/member.php...o&userid=37140
> View this thread: http://www.excelforum.com/showthread...hreadid=568673

it's not 4 untrivial queries.

it is ONE QUERY. MAYBE 2.

And for the record?? i woudln't have to properly normalize the data--
because it would come to me in that format.

-Aaron



Harlan Grove wrote:
> Harlan Grove wrote...
> ...
> >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
> ...
> >
> >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
> ...
>
> So, Aaron, I take it by your silence on this specific question that you
> have NO CLUE how to do this with ANY sort of query. Guess this just
> goes to show that one thing databases don't handle easily is swapping
> values between fields in the same record.
>
> There may be a way to do this with databases. First you'd need to
> convert the first table above to something like
>
> OrigRecNum__ColNum__Value
> _____1__________1_____37
> _____1__________2_____36
> _____1__________3_____26
> _____1__________4_____42
> _____1__________5_____10
> _____1__________6_____23
> _____2__________1______2
> _____2__________2_____48
> _____2__________3______3
> _____2__________4______1
> _____2__________5_____12
> _____2__________6_____28
>
> Then sort this new table by OrigRecNum then Value, both in ascending
> order to get
>
> OrigRecNum__ColNum__Value
> _____1__________5_____10
> _____1__________6_____23
> _____1__________3_____26
> _____1__________2_____36
> _____1__________1_____37
> _____1__________4_____42
> _____2__________4______1
> _____2__________1______2
> _____2__________3______3
> _____2__________5_____12
> _____2__________6_____28
> _____2__________2_____48
>
> Then update the ColNum field to show the count of records with the same
> OrigRecNum and Value <= the current record's Value. The result would
> look like
>
> OrigRecNum__ColNum__Value
> _____1__________1_____10
> _____1__________2_____23
> _____1__________3_____26
> _____1__________4_____36
> _____1__________5_____37
> _____1__________6_____42
> _____2__________1______1
> _____2__________2______2
> _____2__________3______3
> _____2__________4_____12
> _____2__________5_____28
> _____2__________6_____48
>
> And finally put this back into crosstab layout.
>
> F1____F2____F3____F4____F5____F6
> 10____23____26____36____37____42
> _1_____2_____3____13____28____48
>
> Does this help you? Why don't you show us what the queries would look
> like? Dazzle us with how 4 nontrivial queries would be oh, so much more
> efficient than the Excel equivalent of a single array formula filled
> down (one moderately somplicated formula followed by one simple
> operation).

you see harlan.. simple operations like re-sorting? they don't require
you to have a duplicate copy of the data.

and for the record; what percentage of Excel developers use Array
formulas?


5 percent?


-Aaron



Harlan Grove wrote:
> Harlan Grove wrote...
> ...
> >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
> ...
> >
> >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
> ...
>
> So, Aaron, I take it by your silence on this specific question that you
> have NO CLUE how to do this with ANY sort of query. Guess this just
> goes to show that one thing databases don't handle easily is swapping
> values between fields in the same record.
>
> There may be a way to do this with databases. First you'd need to
> convert the first table above to something like
>
> OrigRecNum__ColNum__Value
> _____1__________1_____37
> _____1__________2_____36
> _____1__________3_____26
> _____1__________4_____42
> _____1__________5_____10
> _____1__________6_____23
> _____2__________1______2
> _____2__________2_____48
> _____2__________3______3
> _____2__________4______1
> _____2__________5_____12
> _____2__________6_____28
>
> Then sort this new table by OrigRecNum then Value, both in ascending
> order to get
>
> OrigRecNum__ColNum__Value
> _____1__________5_____10
> _____1__________6_____23
> _____1__________3_____26
> _____1__________2_____36
> _____1__________1_____37
> _____1__________4_____42
> _____2__________4______1
> _____2__________1______2
> _____2__________3______3
> _____2__________5_____12
> _____2__________6_____28
> _____2__________2_____48
>
> Then update the ColNum field to show the count of records with the same
> OrigRecNum and Value <= the current record's Value. The result would
> look like
>
> OrigRecNum__ColNum__Value
> _____1__________1_____10
> _____1__________2_____23
> _____1__________3_____26
> _____1__________4_____36
> _____1__________5_____37
> _____1__________6_____42
> _____2__________1______1
> _____2__________2______2
> _____2__________3______3
> _____2__________4_____12
> _____2__________5_____28
> _____2__________6_____48
>
> And finally put this back into crosstab layout.
>
> F1____F2____F3____F4____F5____F6
> 10____23____26____36____37____42
> _1_____2_____3____13____28____48
>
> Does this help you? Why don't you show us what the queries would look
> like? Dazzle us with how 4 nontrivial queries would be oh, so much more
> efficient than the Excel equivalent of a single array formula filled
> down (one moderately somplicated formula followed by one simple
> operation).

[email protected] wrote...
....
>Can excel even digest data at different granularities?

Got a specific case?

>Everything that was useful in Excel has been inside of Access for 5-6
>years now.

Maybe everything useful to you, since all you seem to need (be able to
understand?) is counting and summing.

So return to a question you haven't managed to answer, how would you
perform matrix multiplication or inversion in Access?

[email protected] wrote...
>you see harlan.. simple operations like re-sorting? they don't require
>you to have a duplicate copy of the data.

Reread your own query. It's reading data from one table and putting it
into a different table with a different number of fields. Wouldn't that
mean two copies of the data?

So you're even unclear on what goes on in databases too, aren't you?

>and for the record; what percentage of Excel developers use Array
>formulas?
>
>5 percent?

Excel USERS? 5%'s about right. Excel DEVELOPERS, as in those people who
create workbooks intended for other people to use, at least 50% use
them and all understand what they do. Your ignorance of them serves as
proof you're no Excel developer.

[email protected] wrote...
>it's not 4 untrivial queries.
>
>it is ONE QUERY. MAYBE 2.

And if it's supposed to be returned to the original 6 fields per record
format?

>And for the record?? i woudln't have to properly normalize the data--
>because it would come to me in that format.

Find a state lotery authority site that provides historical winning
lotto numbers in a normalized format (2 or 3 fields per record, 6
records per draw) rather than a matrix format (6 fields per records or
possibly 7 if the date of the draw were included, so all numbers drawn
in the same records).

You don't have any real world experience with outside data. Most people
receive data in formats over which they have no control AND no access
to any database from which that data may have been drawn. This is a
good example.

So, for the record, you'd have to normalize this data if it comes from
its current sources.

It's absolutely indicative of Aaron's lack of brain.... he thought F1, F2, F3, F4.... in Harlan's example were ACTUAL field names! If he thinks that stupidly, wouldn't we love to see his database crap? (Sarcasm intended).

I worked with a guy a few years ago who was equally flatlined. The guy I worked with, when I told him, in VB code to "sort some fields alphabetically by last name, first name" actually entered into the program's source code, "sort some fields alphabetically by last name, first name" which is what Aaron would do also.

Geeeeee..... I wonder if the guy who worked for me had a middle name of Aaron? Nahhhhhh.... there's only one Aaron (isn't there????)

I love the way Aaron shows us his incompetence.

[email protected] wrote:
> Jay
>
> So maybe you'll have to find someone else to do this..
>
> -Aaron
>


and for the record??
So, you are now giving up on helping the OP resolve the specific
question?

i mean seriously

I think you are losing focus. The McArthur Wheeler story I keep
referencing with the juice comments is amusing. However, for you,
please note the title of the study. It is clearly applicable to you.

"Unskilled and Unaware of It: How Difficulties in Recognizing One's Own
Incompetence Lead to Inflated Self-Assessments"