?
Solved

Advanced Sort Operation

Posted on 2006-04-25
14
Medium Priority
?
208 Views
Last Modified: 2016-02-10
I have a list of data in arranged in columns that I want to sort by how many rows are actually present (non-blank values).  

I have a header row, with ten data rows beneath.  I need to preserve the columns, and just sort the columns by how many values are actually present below the title.  (All ten values present at left and rightward to all values missing).

Thanks for any help,
Sam
0
Comment
Question by:slow1000
  • 6
  • 4
  • 3
  • +1
14 Comments
 
LVL 22

Expert Comment

by:Flyster
ID: 16540734
Not quite sure what you are trying to acomplish. If you want to sort the left colmn, and have the columns sort too, you can use the following macra:

Sub SortColumnMacro()

    Range("A2:A11").Select
    Range("A1:E11").Sort Key1:=Range("A2"), Order1:=xlAscending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
End Sub

The first line selects the ten rows below your header. The second defines the range of columns to the left. Just adjust to the columns you need.

Flyster
0
 
LVL 8

Author Comment

by:slow1000
ID: 16541479
Paste this into Excel as is to see:

year      Value1      Value2      Value3      Value4      Value5      Value6      Value7      Value8      Value9
2005      2.51      2.42      2.84      2.02      2.77      2.92      8.80      8.71      2.16
2004            2.50      2.70      2.00      2.50      3.10      8.80      8.40      1.90
2003            2.50      2.60      1.80      2.50      3.00      8.80      8.00      1.80
2002            2.50            1.70      2.80            8.60      7.80      2.00
2001                              3.50            8.50      7.80      2.00
2000                        1.70      3.50      2.50      8.30      7.70      1.50
1999            2.30                  3.00      2.50      8.70      7.60      1.70
1998                              3.00            8.70      7.50      
1997                              2.81            8.86      7.61      
1996                              3.41            8.60      7.59      

Basically, I want to order the values by how many years of data are actually present, so Values 5,7,8 would be sorted to the left, but value 1, which only has one year of data, should be to the extreme right.

0
 
LVL 8

Author Comment

by:slow1000
ID: 16541497
I can do this by using the function countblank, then filling and sorting horizontally by the countblank row (ascending), but my extant is extremely large, so I'm looking for a macro to do it more easily.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 45

Expert Comment

by:patrickab
ID: 16541974
slow1000,

You can't 'sort' horizontally using the ordinary sort function. Sort works by rows not columns.

You will need a macro to do this.

Patrick
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 16542921
Patrick,
If you choose Data-Sort from the Menu, there is an options button (at least in later versions of Excel; I can't recall if it has always been there) and one of the options is to sort Left to Right.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 16542935
Sam,
Would it be OK for the macro to create the countblank formula for each column and then sort on that (it can be cleared afterwards if you like) or do you want to avoid that?
0
 
LVL 45

Expert Comment

by:patrickab
ID: 16543009
rorya - Thank you for that. By now I should know that - but I do now! - Patrick
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 16543072
Patrick,
No problem - I guess this is why Office 12 is getting such a big makeover of the toolbars! (if I had £1 for every feature I've ever overlooked, I'd be a rich man! :-}
Rory
0
 
LVL 45

Expert Comment

by:patrickab
ID: 16543125
slow1000,

Here's the sorting sub:

Sub sorted()
    Dim rng As Range
    Dim rowes As Integer
    Dim colms As Integer
    Dim r As Integer
    Dim c As Integer
    Dim counter As Integer
   
    rowes = [a65536].End(xlUp).Row - 1
    colms = [iv1].End(xlToLeft).Column - 1
    counter = 0
   
    For c = 2 To colms + 1
        For r = 2 To rowes + 1
            If IsNumeric(Cells(r, c)) = True And Cells(r, c) <> "" Then
                counter = counter + 1
            End If
        Next r
        Cells(rowes + 2, c) = counter
        counter = 0
    Next c

    Set rng = Range(Cells(2, 2), Cells(rowes + 2, colms + 1))
    rng.Select
    Selection.Sort Key1:=Range("B" & rowes + 2), Order1:=xlDescending, Header:=xlGuess _
        , OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _
        DataOption1:=xlSortNormal
    Rows(rowes + 2).ClearContents
    [B2].Select
End Sub

Patrick
0
 
LVL 45

Accepted Solution

by:
patrickab earned 2000 total points
ID: 16543214
It should be this - to include the column headers:

Sub sorted()
    Dim rng As Range
    Dim rowes As Integer
    Dim colms As Integer
    Dim r As Integer
    Dim c As Integer
    Dim counter As Integer
   
    rowes = Sheets(2).[a65536].End(xlUp).Row - 1
    colms = Sheets(2).[iv1].End(xlToLeft).Column - 1
    counter = 0
   
    For c = 2 To colms + 1
        For r = 2 To rowes + 1
            If IsNumeric(Cells(r, c)) = True And Cells(r, c) <> "" Then
                counter = counter + 1
            End If
        Next r
        Cells(rowes + 2, c) = counter
        counter = 0
    Next c

    Set rng = Range(Cells(1, 2), Cells(rowes + 2, colms + 1))
    rng.Select
    Selection.Sort Key1:=Range("B" & rowes + 2), Order1:=xlDescending, Header:=xlGuess _
        , OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _
        DataOption1:=xlSortNormal
    Rows(rowes + 2).ClearContents
   
End Sub

Patrick
0
 
LVL 45

Expert Comment

by:patrickab
ID: 16543273
slow1000,

OK, here it is all working together:

http://my.storenow.net?f=508 

Hope it does what you want.

Patrick
0
 
LVL 8

Author Comment

by:slow1000
ID: 16544900
Thanks for the input, everybody. Patrick, it worked well once again.

Patrick, I am now officially done with using Excel, and am now back to SAS (another fun language, but at least I'm competent with it), so no more MS Office question.  Anyway, the data I'm working with was stretching the limits of Excel in terms of sheer size.
0
 
LVL 45

Expert Comment

by:patrickab
ID: 16546882
slow1000 - Thanks for the grade. Good luck with SAS - that last company that I knew of that adopted SAS amid much blowing of trumpets, was closed within 6 months of installing it! So I hope you fare better. - Patrick
0
 
LVL 8

Author Comment

by:slow1000
ID: 16550146
Patrick,
Unfortunately, I will have no such luck as to get away from SAS--I normally work with huge datasets and odd statistical and econometric methods.  Only Stata really can beat out SAS for doing what I do, but it's not as versatile in other areas, so I'm stuck with SAS until the next big thing comes out, but I'm not holding my breath.  I just happenned to be working on a project using Eviews, which is quite picky about how it wants data, so I needed the data cleaned up before importing it, and Excel is what I have at home.  Next stop, fun with ESRI's Arcview for a consulting project, then Gauss this fall.
Sam
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
When you discover the power of the R programming language, you are going to wonder how you ever lived without it! Learn why the language merits a place in your programming arsenal.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

755 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question