Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formatting? (Excel2000)

    Hi Loungers, using this formula to compare to rows of data: =isnumber(match(a2, $e$2:$e$2000, 0)). Where there is no match should be a False, otherwise True. The 2 different lists have the same numbers, but the result comes back false. I individually compare the numbers (a2=e2) and it still says false. I have both columns formatted as General.

    Any ideas?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formatting? (Excel2000)

    MATCH compares the underlying values stored in the cells, not the values as displayed, unless you have ticked 'Precision as Displayed' in the Calculate tab of Tools | Options... (not recommendable in general).

    If the values in column A and E are the result of calculations, they may differ by a very small amount. So can you tell us a bit more about the contents of columns A and E, or perhaps post a cut-down version of the workbook? (Delete sensitive or proprietary information before posting.)

  3. #3
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting? (Excel2000)

    Figured it out. Had to copy, paste special, values, add to get it to work. The numbers were dumps out of an Access database, that is about all I know about them.

    Any idea on why I had to do this? I tried paste special, values (no add) and would not work...

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formatting? (Excel2000)

    Data imported from another application often behave oddly in Excel. Numeric values are frequently interpreted as text. I guess that by specifying the 'add' option, you forced Excel to convert those text values to numbers.

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Formatting? (Excel2000)

    Another shortcut to fix data which has been imported and behaves as text is to select the column, and via the Menu, Data | Text To Columns | Fixed Width | Finish. Can only be done on one column at a time.
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

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