Formatting? (Excel2000)

Topic

New Reply

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?

Reply | Quote

Replies

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.)

Reply | Quote

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…

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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…

Reply | Quote

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.)

Reply | Quote