Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: sort and clean up a list

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Location
    Arizona
    Posts
    68
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    anyone know how I can sort this mess...
    e.g.

    a101
    ----
    aprt
    a102
    b103
    >>>>
    a103
    c201
    ====
    c203
    build


    I want to sort to this:

    a101
    a102
    a103
    b103
    c201
    c203

    I don't think isnumeric will work for me...I'm really stuck with this one!

    Any help greatly appreciated!

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,653
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-05-08 11:15, chouston wrote:
    anyone know how I can sort this mess...
    e.g.

    a101
    ----
    aprt
    a102
    b103
    >>>>
    a103
    c201
    ====
    c203
    build


    I want to sort to this:

    a101
    a102
    a103
    b103
    c201
    c203

    I don't think isnumeric will work for me...I'm really stuck with this one!

    Any help greatly appreciated!
    In a column next to your data, enter:

    =ISNUMBER(RIGHT(A1)+0)+0

    and copy down.

    Then sort on the second column descending then the first column ascending.


  3. #3
    Board Regular
    Join Date
    Apr 2002
    Location
    Arizona
    Posts
    68
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks Aladin!
    I was trying to use vba to do this, but I was able to use your advice and make some progress, I'm able to identify the cells I want using the following code, but am having trouble deleting the row. Perhaps you (or anyone) might be able to suggest the proper syntax:

    cleanrange = ActiveWorkbook.Sheets("data").Range(Cells(1, 1), Cells(4000, 1))

    For Each cel In cleanrange
    aptunit = Right(cel, 3)
    If IsNumeric(aptunit) = False Then
    ActiveWorkbook.Sheets("data").Range(cel).EntireRow.Delete
    End If
    Next cel

    Many thanks

  4. #4
    Board Regular
    Join Date
    May 2002
    Posts
    147
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    If all the data is in one column and is how you list then try this:

    if there is not a title row, insert one

    copy the entire column to another column
    data/text to columns/fixed with
    insert a break line after the first character only and remove any others

    Highlight the entire range
    data/filter/auto filter

    in the third column should now only be numbers or characters

    click on the dropdown, custom, is greater than 0

    copy this, paste it into another workbook, and delete any unnecessary columns

  5. #5
    Board Regular
    Join Date
    Apr 2002
    Location
    Arizona
    Posts
    68
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Could someone suggest vba function or syntax I can use to delete the entirerow if not numeric?

    --
    cleanrange = ActiveWorkbook.Sheets("data").Range(Cells(1, 1), Cells(4000, 1))

    For Each cel In cleanrange
    aptunit = Right(cel, 3)
    If IsNumeric(aptunit) = False Then
    ====> "I want to delete this row!!!"
    End If
    Next cel
    --

    Thanks

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •