GordonMasson
asked on
Sorting a spreadsheet by mixed data column
Hi
I am looking for a bit of code that will sort a spreadsheet based on a column of data (column H)
The column has both dates and text in it and I need to sort these as follows…….
Say the data in the column is
20 February 2011
CLOSED
n/a
n/a
CLOSED
01 March 2011
12 December 2010
06 March 2011
I need it to sort the sheet so that the column eventually reads…….
06 March 2011
01 March 2011
20 February 2011
12 December 2010
n/a
n/a
CLOSED
CLOSED
Thanks
I am looking for a bit of code that will sort a spreadsheet based on a column of data (column H)
The column has both dates and text in it and I need to sort these as follows…….
Say the data in the column is
20 February 2011
CLOSED
n/a
n/a
CLOSED
01 March 2011
12 December 2010
06 March 2011
I need it to sort the sheet so that the column eventually reads…….
06 March 2011
01 March 2011
20 February 2011
12 December 2010
n/a
n/a
CLOSED
CLOSED
Thanks
Bit of a fudge perhaps, but you could assign numbers in an adjacent column, e.g. 1 for the dates, 2 for the n/a, 3 for Closed and then sort by that column.
ASKER
Hi
Cople of problems here.
jimyX
As per above i need the resultant sort to be:
06 March 2011
01 March 2011
20 February 2011
12 December 2010
n/a
n/a
CLOSED
CLOSED
Not
n/a
n/a
CLOSED
CLOSED
06 March 2011
01 March 2011
20 February 2011
12 December 2010
StephanJR
The dates and status change depending on other variables so having another "order" column doesnt work.
i.e. 20 February 2011 could change to CLOSED
Cople of problems here.
jimyX
As per above i need the resultant sort to be:
06 March 2011
01 March 2011
20 February 2011
12 December 2010
n/a
n/a
CLOSED
CLOSED
Not
n/a
n/a
CLOSED
CLOSED
06 March 2011
01 March 2011
20 February 2011
12 December 2010
StephanJR
The dates and status change depending on other variables so having another "order" column doesnt work.
i.e. 20 February 2011 could change to CLOSED
So in general you want to sort dates in reverse order, followed by n/a and CLOSED?
ASKER
Perhaps its not clear.
I need to sort as above.... the most recent date first untill you run out of dates then all the "n/a"'s then all the "CLOSED"'s
I need to sort as above.... the most recent date first untill you run out of dates then all the "n/a"'s then all the "CLOSED"'s
I think that's what I said? You could use a formula, e.g. if your data were in A1 and A8, then this in B1:B8 and then sort by column B. Could be automated with VBA if it's a viable approach.
=IF(ISNUMBER(A1),VALUE(A1) ,LOOKUP(A1 ,{"CLOSED" ,"n/a"},{1 ,2}))
=IF(ISNUMBER(A1),VALUE(A1)
ASKER
Hi Stephen
Ok that works but i would really like to do it in VBA and without adding a second column.
I did manage to do it in a long winded way in VBA.....must be a better way to do it?
In this case i am sorting by column H
Sort by the column values a-z
Filter out Closed and n/a
Sort by the column values z-a
Filter column to hide Closed and n/a
Sort by column values z-a
Clear filters
ActiveWorkbook.Worksheets( "Register" ).sort.Sor tFields.Cl ear
ActiveWorkbook.Worksheets( "Register" ).sort.Sor tFields.Ad d Key:=Range( _
"H3:H52"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets( "Register" ).sort
.SetRange Range("A2:AT52")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveSheet.Range("$A$2:$A S$52").Aut oFilter Field:=8, Criteria1:=Array("=" _
), Operator:=xlFilterValues, Criteria2:=Array(0, "3/15/2011")
ActiveWorkbook.Worksheets( "Register" ).sort.Sor tFields.Cl ear
ActiveWorkbook.Worksheets( "Register" ).sort.Sor tFields.Ad d Key:=Range( _
"H3:H52"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets( "Register" ).sort
.SetRange Range("A2:AT52")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveSheet.Range("$A$2:$A S$52").Aut oFilter Field:=8, Criteria1:="=Closed" _
, Operator:=xlOr, Criteria2:="=n/a"
ActiveWorkbook.Worksheets( "Register" ).sort.Sor tFields.Cl ear
ActiveWorkbook.Worksheets( "Register" ).sort.Sor tFields.Ad d Key:=Range( _
"H3:H52"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets( "Register" ).sort
.SetRange Range("A17:AT92")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveSheet.Range("$A$2:$A S$52").Aut oFilter Field:=8
Application.ScreenUpdating = True
Ok that works but i would really like to do it in VBA and without adding a second column.
I did manage to do it in a long winded way in VBA.....must be a better way to do it?
In this case i am sorting by column H
Sort by the column values a-z
Filter out Closed and n/a
Sort by the column values z-a
Filter column to hide Closed and n/a
Sort by column values z-a
Clear filters
ActiveWorkbook.Worksheets(
ActiveWorkbook.Worksheets(
"H3:H52"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets(
.SetRange Range("A2:AT52")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveSheet.Range("$A$2:$A
), Operator:=xlFilterValues, Criteria2:=Array(0, "3/15/2011")
ActiveWorkbook.Worksheets(
ActiveWorkbook.Worksheets(
"H3:H52"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets(
.SetRange Range("A2:AT52")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveSheet.Range("$A$2:$A
, Operator:=xlOr, Criteria2:="=n/a"
ActiveWorkbook.Worksheets(
ActiveWorkbook.Worksheets(
"H3:H52"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets(
.SetRange Range("A17:AT92")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveSheet.Range("$A$2:$A
Application.ScreenUpdating
In VBA you could add the column, sort and then delete (or hide) the second column without the user seeing. Might be a bit simpler than your approach. Will mock something up.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Stephen
Well that does it for me ..... thanks very much.
I did have one problem because the code deleted a column with data in it so i had to modify it to insert a new column.....
Sub y()
Application.ScreenUpdating = False
With Range("h3", Range("h3").End(xlDown))
.Offset(, 1).EntireColumn.Insert
.Offset(, 1).Formula = "=IF(ISNUMBER(h3),VALUE(h3 ),LOOKUP(h 3,{""CLOSE D"",""n/a" "},{1,2})) "
.Resize(, 2).sort key1:=Range("i1"), order1:=xlDescending, Header:=xlNo
.Offset(, 1).EntireColumn.Delete
End With
Application.ScreenUpdating = True
End Sub
Thanks again
Well that does it for me ..... thanks very much.
I did have one problem because the code deleted a column with data in it so i had to modify it to insert a new column.....
Sub y()
Application.ScreenUpdating
With Range("h3", Range("h3").End(xlDown))
.Offset(, 1).EntireColumn.Insert
.Offset(, 1).Formula = "=IF(ISNUMBER(h3),VALUE(h3
.Resize(, 2).sort key1:=Range("i1"), order1:=xlDescending, Header:=xlNo
.Offset(, 1).EntireColumn.Delete
End With
Application.ScreenUpdating
End Sub
Thanks again
ASKER
Hi
I realise that this question has been closed and i will open a new question to assign points if thats how you do this but i have a slight problem with the code that i am using that i would like to change.
The code above uses h3 which is the top cell of the data i want to sort by but if for any reason further columns are inserted then this code isnt going to work.
Can you tell me how to update the code so that it uses the range name for h2 which is LastChange rather than using h3.
Also can you let me know how i go about awarding points for your answer.
Thanks for your help
I realise that this question has been closed and i will open a new question to assign points if thats how you do this but i have a slight problem with the code that i am using that i would like to change.
The code above uses h3 which is the top cell of the data i want to sort by but if for any reason further columns are inserted then this code isnt going to work.
Can you tell me how to update the code so that it uses the range name for h2 which is LastChange rather than using h3.
Also can you let me know how i go about awarding points for your answer.
Thanks for your help
n/a
n/a
CLOSED
CLOSED
06 March 2011
01 March 2011
20 February 2011
12 December 2010