Problem With Sorting

[kafka]

I am trying to sort a worksheet so that all rows which are empty in a particular cell ("Sold" In the sample) appear at the bottom of the worksheet while all the rows which contain a value in that cell are then sorted under Artist then Title then Format. Unfortunately, whilst I can sort the rows simply on the Sold cell being empty or containing data, I cannot sort the worksheet so that all rows are in Artist, Label, Format order and all the empty Sold cells appear at the bottom. It seems that I can only sort them either ian alphabetical order or on Sold.

I have attached a sample worksheet. The actual worksheet contains hundred so rows. I should add that I am trying to record a macro (rather than write one) and if you give me some code I won't know where to insert them in the recorded macro.

 

[Alex Blakenburg]

You will need a helper column to do what you want.
eg Column heading HasValue
=If(Sold<>"","Yes","No")
Then you sort on that column first "descending" and then do all your other sorts.

 

[kafka]

Thanks. I've always recorded macros rather than written them. Are you able to tell me where in the sample macro below I should insert the code you suggest?

Sub Test()
'
' Test Macro
'

'
Range("A1:I15").Select
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("I1:I15") _
, SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A1:A15") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("B1:B15") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("A1:I15")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Application.Goto Reference:="Test"
End Sub

 

[Alex Blakenburg]

Normally you would just add a column to your data with the formula.
If you are going to use code to do the sort anyway I can add it into the code.

You seem to have a blank line after your heading row, this is normally bad practice. It means that Excel can't work out the Current Region which is used to determine the Pivot Data area, sort & filter areas automatically. It won't stay there on a sort.

I am in Australia and will have a look at it later today.

 

[kafka]

I added the HasValue column as you suggested and entered the formula. I then sorted on that column. So far, so good. When I then sorted on Artist then Title the worksheet ceased to be sorted on HasValue and was simply sorted in alphabetical order. I think I am missing something!

 

[Peter_SSs]

I then sorted on that column. So far, so good. When I then sorted on Artist then Title t

You need to include the HasValue sort as the first item in any sort you do if you want those cells at the bottom. It is not a separate one-off sort.
It would be like recording a macro to sort like this

 

[kafka]

Thanks. I have tested the macro on my sample worksheet and it does the trick. I will have to think about this as my original macro seemed to do the same thing. It didn't have a HasValue Column, rather it sorted on the column (I) which either contained a value or was blank, placing the rows with a value at the top. It then sorted the worksheet on Column A then Column B, resulting in a worksheet in which the rows where Column I was blank appeared in alphabetical order under the rows where Column I contained a value but were not in alphabetical order. Not to worry!

I should add that this macro will actually form the last part of a larger macro which joins two similar worksheets.

Thank you for your invaluable assistance.

 

[Alex Blakenburg]

my original macro seemed to do the same thing.
It didn't have a HasValue Column, rather it sorted on the column (I) which either contained a value or was blank, placing the rows with a value at the top.

Each sort has the effect of collecting like items together eg all artists with the same name.
The next level of sort sorts as a subset of the previous sort and now collects like items with 2 columns in common eg all artists with the same name & same title.
The next level again sorts as a subset of the previous 2 sorts collecting like items with 3 columns in common eg all artists with the same name & same title & same format.

What the HasValue column does when you put it as the very first sort column, is to collect the rows into 2 groups, those with a Value ("Yes") and those without ("No"). Your artist sort is now within those 2 groups (Yes by artist and No by artist). The titles sort within those 2 groups and within artists (Yes by artist by title, No by artist by title)

the rows where Column I was blank appeared in alphabetical order under the rows where Column I contained a value but were not in alphabetical order.

By sorting on the actual Sold value you are getting a group for every value in the Sold column. So it will sort artist alphabetically with $100, then again $ 99 etc for every value, the only big block being those with no value. eg $ 100 by artist by title, $ 99 by artist by title ....... blank by artist by title

If you were to sort on a unique index no such as the row number any additional sorts would do nothing.

Hope this help.

 

[kafka]

Thanks for that explanation. Before I retired twenty years ago my job had been, for at least ten years, building reporting systems using Excel and Access. I thought I was a bit of an Excel guru and was, amazingly enough, considered a "subject matter expert". How wrong I was!

Thanks again for your help. A true guru!

 

[Alex Blakenburg]

I don't know about that, you are probably just a bit out of practice. You could always join us in answering questions
Glad we could help.