Sort column by VBA or formula

[Russk68]

Hi All

I need to sort column A first by the lowest number. Column A is always a number.Then by Column B and is always a word with many duplicates.

Example:
Sheet 1

_____A______B
1____21_____Red
2____22_____Red
3____23_____Red
4____103____Green
5____104____Green
6____105____Green
7____67_____Blue
8____13_____Blue
9____74_____Blue
10___32_____Green
11___14_____Blue
12___82_____Red
13___1______Red
14___2______Red
15___3______Red
16___55_____Blue
17___56_____Blue
18___57_____Blue

Result is just the number in column A in sheet 2:
_____A
1_____1 (Red)
2_____2 (Red)
3_____3 (Red)
4____21 (Red)
5____22 (Red)
6____23 (Red)
7____82 (Red)
8____13 (Blue)
9____14 (Blue)
10___55 (Blue)
11___56 (Blue)
12___57 (Blue)
13___67 (Blue)
14___74 (Blue)
15___32 (Green)
16__103 (Green)
17__104 (Green)
18__105 (Green)

If this is best done with a macro, I would like it to run when the sheet is calculated.
I know this can be done using Sort, but I need it to be automated.

Thank you!

Russ

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet.

Private Sub Worksheet_Calculate()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Application.ScreenUpdating = True
    Sheets("Sheet1").Sort.SortFields.Clear
    Sheets("Sheet1").Sort.SortFields.Add Key:=Range("B1:B" & LastRow) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    Sheets("Sheet1").Sort.SortFields.Add Key:=Range("A1:A" & LastRow) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With Sheets("Sheet1").Sort
        .SetRange Range("A1:B" & LastRow)
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Application.ScreenUpdating = True
End Sub

 

[Russk68]

Hi Mumps
I see that this sorts column A and B in sheet 1 perfectly. My goal though is to leave sheet 1 intact and have column A in sheet 2 just show the sorted numbers from sheet 1.

ADJUSTMENT: I need to adjust Sheet1. Column A is still the number but starts at A3. Column K is the word and starts at K3.

I appreciate your help!

Thanks!

 

[mumps]

You said that you wanted the macro "to run when the sheet is calculated". Is it Sheet1 or Sheet2 that is calculated? Since the data is in columns A and K in Sheet1, do you want the end result in columns A and K of Sheet2 or in columns A and B of Sheet2?

 

[Russk68]

I would like the macro in sheet 2 to run when it is calculated.
I need the end result to be in Sheet 2 column A only. I just need the number to appear in column A (Start in A3). I don't need the name to appear in sheet 2. Column K in sheet 1 is just needed for sorting purposes.


Thank you!

 

[mumps]

Try this macro in the code module for Sheet2:

Private Sub Worksheet_Calculate()
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Dim LastRow As Long, srcWS As Worksheet
    Set srcWS = Sheets("Sheet1")
    Columns("B:B").Insert Shift:=xlToRight
    Columns("A").ClearContents
    With srcWS
        .Range("A3", srcWS.Range("A" & srcWS.Rows.Count).End(xlUp)).Copy Cells(1, 1)
        .Range("K3", srcWS.Range("K" & srcWS.Rows.Count).End(xlUp)).Copy Cells(1, 2)
    End With
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Application.ScreenUpdating = True
    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add Key:=Range("B1:B" & LastRow) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveSheet.Sort.SortFields.Add Key:=Range("A1:A" & LastRow) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveSheet.Sort
        .SetRange Range("A1:B" & LastRow)
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Columns("B").Delete
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

 

[Russk68]

I will try this later tonight.
Thank you very much for your help!

 

[Russk68]

This is really close to what I need. I just need the results in Sheet 2 to start in A3. I tried to tweak your code but didn't get the proper results.
Also, the macro doesn't run when I hit calculate. I can run it when I hit play from the developer tab.
Thank you for your help!

 

[mumps]

This will make the results in Sheet 2 start in A3. The macro is a Worksheet_Calculate event and is triggered when any formula in Sheet2 is calculated by changing a value referenced in the formula. For example, if you have this formula in Sheet2: =sum(A1:B1) in any cell, if you change either the value in A1 or B1 the macro will be triggered because the formula is then calculated. If you want to trigger the macro manually by clicking a button, then we have to change our approach.

Private Sub Worksheet_Calculate()
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Dim LastRow As Long, srcWS As Worksheet
    Set srcWS = Sheets("Sheet1")
    Columns("B:B").Insert Shift:=xlToRight
    Columns("A").ClearContents
    With srcWS
        .Range("A1", .Range("A" & .Rows.Count).End(xlUp)).Copy Cells(3, 1)
        .Range("K1", .Range("K" & .Rows.Count).End(xlUp)).Copy Cells(3, 2)
    End With
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Application.ScreenUpdating = True
    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add Key:=Range("B3:B" & LastRow) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveSheet.Sort.SortFields.Add Key:=Range("A3:A" & LastRow) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveSheet.Sort
        .SetRange Range("A3:B" & LastRow)
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Columns("B").Delete
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

 

[Russk68]

Almost there! Hope I'm not asking too much here.
I believe the macro is sorting column B alphabetically from the tests that I have tried.
I need column A to sort by the lowest number first. Then sort column B with the matching names.
Example:
11___Apple
12___Apple
1___Banana
2____Banana
7____Orange
8____Orange
31___Banana
32___Orange
33___Apple
The desired result would be:
1
2
31
7
8
32
11
12
33
So, if I were to swop Apple with Banana, the result above would be the same.
Thanks for all your help so far!