Advanced Sort Operation

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.

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.

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

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.

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?

rorya - Thank you for that. By now I should know that - but I do now! - Patrick

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

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

slow1000,

OK, here it is all working together:

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

Hope it does what you want.

Patrick

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.

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