• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 221
  • Last Modified:

How can I sort the attached spreadsheet so that the entries in the Applications column that say "[Can't connect to or ping]" are grouped at the bottom of the spreadsheet?

How can I sort the attached spreadsheet so that the entries in the Applications column that say "[Can't connect to or ping]" are grouped at the bottom of the spreadsheet?

I need these to be moved and for the corresponding data in the other rows to remain properly grouped together.
Inventory.xlsx
0
Knowledgeable
Asked:
Knowledgeable
  • 2
3 Solutions
 
zorvek (Kevin Jones)ConsultantCommented:
One way to do it is with a helper column containing this formula:

   =IF(D2="[Can't connect to or ping]","ZZZZ",D2)

Sort on the helper column.

See attached.

Kevin
Inventory.xlsx
0
 
nutschCommented:
Try this code

Sub Macro4()
 Application.ScreenUpdating = False

    Columns("A:A").Insert
    Range("A2:A" & Cells(Rows.Count, 2).End(xlUp).Row).FormulaR1C1 = "=IF(RC[4]=""[Can't connect to or ping]""," & Rows.Count & ",ROW())"
    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add Key:=Range("A2"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortTextAsNumbers
    With ActiveSheet.Sort
        .SetRange Range("A2:F22")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Columns("A:A").Delete
    
    
    Application.ScreenUpdating = True
    
End Sub

Open in new window


Thomas
0
 
nutschCommented:
My code does the same thing as the helper column mentioned by Kevin, but using the below formula, doing the sort and removing the column afterwards.

   =IF(D2="[Can't connect to or ping]","ZZZZ",row())

Thomas
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now