I have two spreadsheet which I would like to compare. One has numeric data,
the other has the same data but the references codes have been entered as
alpha. Does anyone know how this can be converted to numeric?
Many thanks for your help.
I have two spreadsheet which I would like to compare. One has numeric data,
the other has the same data but the references codes have been entered as
alpha. Does anyone know how this can be converted to numeric?
Many thanks for your help.
Not sure, but maybe as a start, you could try something along these lines
using VLOOKUP and CODE() ?
Assume the sample (numeric) data below is
in Sheet1 cols A and B,
with the numeric refcodes in col A
RefCode Field1
65 Text1
66 Text2
67 Text3
etc
And you have in Sheet2,
the alphas as the RefCodes in col A
RefCode Field1
A Text1
B Text2
C Text3
etc
Put 2 labels into C1:D1 : Compare, Result
Put in C2: =VLOOKUP(CODE(TRIM(A2)),Sheet1!A:B,2,0)
Put in D2: =TRIM(B2)=TRIM(C2)
Select C2:D2 and fill down
Col C ("Compare") extracts over the matched items for the RefCodes (A,B,C)
from the col Field1 in Sheet1 for comparison, while col D (Result") gives
the result of the comparison of col C with what you have in col B in Sheet2
(TRUE = yes, matched ; FALSE = no, does not match)
For the sample data above, what you'll get is:
RefCode Field1 Compare Result
A Text1 Text1 TRUE
B Text2 Text2 TRUE
C Text3 Text3 TRUE
etc
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Vicki" <[email protected]> wrote in message
news:[email protected]...
> I have two spreadsheet which I would like to compare. One has numeric
data,
> the other has the same data but the references codes have been entered as
> alpha. Does anyone know how this can be converted to numeric?
>
> Many thanks for your help.
On Sun, 20 Mar 2005 21:45:02 -0800, "Vicki" <[email protected]>
wrote:
>I have two spreadsheet which I would like to compare. One has numeric data,
>the other has the same data but the references codes have been entered as
>alpha. Does anyone know how this can be converted to numeric?
>
>Many thanks for your help.
If I understand you correctly:
Information that looks like a number but is really TEXT can be changed to a
number by performing some kind of arithmetic operation.
So, for a comparison, if the numeric data is in A1 and the TEXT data is in B1,
and both of them are 12345; the formula:
=A1=B1 :FALSE
=A1=--B1 :TRUE
The double unary preceding B1 forces Excel to convert it to a number.
--ron
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks