Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / New Users / April 2004

Tip: Looking for answers? Try searching our database.

Sorting multidimensional tables/multiple selections

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
paivoh - 12 Apr 2004 16:37 GMT
I have several big matrices (1200*50) on different worksheets. It is a
time-series data, so row headings are dates and column headings are
firms. Cells are stock earnings, every worksheet using a different
principle for calculating them.

I need to sort this data like this: every row (daily revenues) in the
first matrix (1. worksheet) in a descending/ascending order and all the
other matrices should sort accordingly.

This is because I need for example the smallest five values from the
first matrice's rows and then the according values of those five firms
from the other matrices.

So I should find a way to bind the cells together somehow... It would
even be tolerable if I could do this with two matrices only, or with a
3-d table they would constitute. Of course it would be easier to use a
method for a n-dimensional table.

Some other way? If every cell would "remember" its column heading
("firm") then maybe I could use that to find the necessary cells from
the other matrices...

Is this possible in Excel? Some other program (free/free trial) that
could be useful?

Help with this is desperately needed and would be greatly appreciated!
Dave Peterson - 12 Apr 2004 23:05 GMT
I don't know of any addin that does what you want, but you could record a macro
when you sorted one worksheet manually.  Then run that macro against all (or
some of) the worksheets in your workbook.

I recorded a macro and tweaked it to sort the same columns (A:L) and used the
data in column A to determine the last row to sort.  I also said that headers
were in Row 1 only.

The first sub sorts all the worksheets in that workbook.

Option Explicit
Sub testme()
   Dim wks As Worksheet
   For Each wks In ActiveWorkbook.Worksheets
       With wks
           .Range("a1:L" & .Cells(.Rows.Count, "A").End(xlUp).Row).Sort _
               key1:=.Range("a1"), order1:=xlDescending, _
               header:=xlYes, MatchCase:=False
       End With
   Next wks
End Sub

If you don't want to sort all the sheets, you can specify which ones you want
with something like this:

Sub testme2()
   Dim wks As Worksheet
   Dim myWksNames As Variant
   
   myWksNames = Array("sheet1", "sheet2")
   
   For Each wks In ActiveWorkbook.Worksheets(myWksNames)
       With wks
           .Range("a1:L" & .Cells(.Rows.Count, "A").End(xlUp).Row).Sort _
               key1:=.Range("a1"), order1:=xlDescending, _
               header:=xlYes, MatchCase:=False
       End With
   Next wks
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

====
And for a different approach, you may want to take a look at the worksheet
function =Rank().  Maybe you don't even need to sort all your data.

> I have several big matrices (1200*50) on different worksheets. It is a
> time-series data, so row headings are dates and column headings are
[quoted text clipped - 26 lines]
> Excel Templates, Training, Add-ins & Business Software Galore!
> Free Excel Forum http://www.ozgrid.com/forum ***

Signature

Dave Peterson
ec35720@msn.com

 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.