Solved

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?

Posted on 2011-03-16
3
212 Views
Last Modified: 2012-05-11
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
Comment
Question by:Knowledgeable
  • 2
3 Comments
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 167 total points
ID: 35152905
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
 
LVL 39

Assisted Solution

by:nutsch
nutsch earned 333 total points
ID: 35152909
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
 
LVL 39

Assisted Solution

by:nutsch
nutsch earned 333 total points
ID: 35152919
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

This article will show you how to use shortcut menus in the Access run-time environment.
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ

828 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