Solved

Posted on 2006-04-25
Medium Priority
208 Views
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
Question by:slow1000
• 6
• 4
• 3
• +1

LVL 22

Expert Comment

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

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

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

LVL 45

Expert Comment

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

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

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

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

LVL 85

Expert Comment

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

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

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

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

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

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

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

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â€¦
###### Suggested Courses
Course of the Month13 days, 11 hours left to enroll