Auto Sort Macro

**lj8675309** · 11-14-2012, 03:30 PM

Hi!

I have never used a macro before but am hoping to start because sorting this spreadsheet is beginning to drive me wild!

I am hoping to sort by:

1) Review Status (Pending, Data Collection, Analysis, Decision, Implementation -- in that order)
2) Office (CBA)
3) Program (ABC)

Any ideas on a macros I can use to make this happen? Most of the entire spreadsheet is VLOOKUPs and the Review status is color coded. I have been sorting on values and color (Review Status) to make this work.

A sanitized portion of the spreadsheet is attached.

Thanks in advance for any help!

Excel Sort Help.xlsx

**jeffreybrown** · 11-14-2012, 03:56 PM

How about this which is just a normal module...

Looks like you had row two as jus blank so I removed it. In column L, I use a helper column to establish the order of the "Review Status".

In that helper column biggining with L2 and copied down...

=LOOKUP(K2,{"Analysis","Data Collection","Decision","Implementation","Pending"},{3,2,4,5,1})

Sub SortData()
    Dim LR As Long
    LR = Range("A" & Rows.Count).End(xlUp).Row
    With ActiveSheet.Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range("L2:L" & LR), SortOn:=xlSortOnValues, Order:=xlAscending
        .SortFields.Add Key:=Range("A2:A" & LR), SortOn:=xlSortOnValues, Order:=xlDescending
        .SortFields.Add Key:=Range("B2:B" & LR), SortOn:=xlSortOnValues, Order:=xlAscending
        .SetRange Range("A1:L" & LR)
        .Header = xlYes
        .Orientation = xlTopToBottom
        .Apply
    End With
End Sub

**tandwfund** · 11-14-2012, 06:18 PM

Hi,

Depending how you are using the data -- Excel 2010 allows you to make a pivot table and then add 3 Slicers to it. They could be Status, Office, and Program. This would allow anyone to filter the data by "button". If the pivot table includes the money column, each filter will give appropriate value.

Terry

**lj8675309** · 11-15-2012, 04:09 PM

Hi Terry,

A pivot table wouldn't work well because a lot of the data is wordy. I sanitized the selection that I shared because of the procurement sensitive nature of the data, but that's just a snip of a large inventory with lots of description-heavy data. However, I am not familiar with slicers and am always looking to snaz up my pivot tables so I will look into them!

Thanks!

L

**lj8675309** · 11-14-2012, 05:09 PM

Hi Jeff!

Thanks so much for the prompt reply! With a tiny bit of easy tweaking it worked! The only thing that is a little off is that ABC order for the review status is not the correct order. I will fix this by adding a number in front of the status in the spreadsheet that the data is pulled from and change it to sort in descending order. Coincidentely, the number will coincide with the phase of the process so this is just perfect!

Thanks again!

You rock,

lindsay

**jeffreybrown** · 11-14-2012, 05:15 PM

Hi Lindsay,

Glad it works out for you and you are very welcome. Thanks for the feedback.

================================================================

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.