Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Filter on number (Office 2000)

    Hi,
    I have a column that has names, number (general format) and amount (currency format). These were copied and pasted from a pdf file. I need to move the number into the next column and the amount into another column. I tried using the filter but for some reason I didn't get any results. I tried for example using the "begins with" $ for the amount. and or "equal to 300???" or "equal to 400???" but still didn't get any results. The numbers either start with 300??? or 400???. When I used greater than one I got all the numbers and amounts but couldn't move them to another column, If I tried copy and paste but they were not in the original row.

    Any suggestions?
    Thank you
    Louise

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Filter on number (Office 2000)

    Did you try Greater than or equal to 300000 as the filter?
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter on number (Office 2000)

    Hi,
    Thank you for your reply.
    Yes I did and it did filter out the numbers but I couldn't copy and paste them to a new column and still keep the same place in the column. In other words, a number that was in row 500 for example was now in row 200. They need to stay in the same place relative to the original. I also couldn't move them because I receive the "multiple selection" error.
    Louise

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Filter on number (Office 2000)

    Filter will copy as multiple selections since it ignores the non-visible rows.

    Why not use a formula in the desired column while not filter.
    To get the "number":
    =IF(AND(ISNUMBER(A1),A1>300000),A1,NA())

    To get the currency value:
    =IF(AND(ISNUMBER(A1),A1<=300000),A1,NA())

    Copy them down the rows. Then copy the columns, pastespecial them as values to remove the formulas.
    To get rid of the errors you can use, edit - goto - special select values and errors and then press <del>

    Steve

  5. #5
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter on number (Office 2000)

    Hi,

    Thank you very much. Will keep that in my tips and tricks. <img src=/S/smile.gif border=0 alt=smile width=15 height=15> What I ended up doing was copy the entire column and pasted it into adjoining columns. Then I filtered what I didn't want and deleted the info. What was left I could work with.

    Thanks again.
    Louise

Posting Permissions

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