Solved

Sorting a spreadsheet by mixed data column

Posted on 2011-03-17
11
196 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:GordonMasson
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
11 Comments
 
LVL 24

Expert Comment

by:jimyX
ID: 35157230
It can be sorted descendant as follows:
n/a
n/a
CLOSED
CLOSED
06 March 2011
01 March 2011
20 February 2011
12 December 2010
0
 
LVL 24

Expert Comment

by:StephenJR
ID: 35157240
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.
0
 

Author Comment

by:GordonMasson
ID: 35163930
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
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 24

Expert Comment

by:StephenJR
ID: 35164405
So in general you want to sort dates in reverse order, followed by n/a and CLOSED?
0
 

Author Comment

by:GordonMasson
ID: 35165522
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
0
 
LVL 24

Expert Comment

by:StephenJR
ID: 35165802
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}))
0
 

Author Comment

by:GordonMasson
ID: 35166644
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.SortFields.Clear
    ActiveWorkbook.Worksheets("Register").sort.SortFields.Add 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:$AS$52").AutoFilter Field:=8, Criteria1:=Array("=" _
        ), Operator:=xlFilterValues, Criteria2:=Array(0, "3/15/2011")
    ActiveWorkbook.Worksheets("Register").sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Register").sort.SortFields.Add 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:$AS$52").AutoFilter Field:=8, Criteria1:="=Closed" _
        , Operator:=xlOr, Criteria2:="=n/a"
    ActiveWorkbook.Worksheets("Register").sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Register").sort.SortFields.Add 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:$AS$52").AutoFilter Field:=8
Application.ScreenUpdating = True
0
 
LVL 24

Expert Comment

by:StephenJR
ID: 35166720
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.
0
 
LVL 24

Accepted Solution

by:
StephenJR earned 500 total points
ID: 35166748
Illustration in attachment, obviously would need tweaking for your actual data.
Book5.xls
0
 

Author Comment

by:GordonMasson
ID: 35167006
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(h3,{""CLOSED"",""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
0
 

Author Comment

by:GordonMasson
ID: 35205616
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
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

738 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