Comparing slightly differing columns
 

mHi,

I need to compare two columns of account numbers that come from diferent
sources. One column has the account number correctly displayed, the other has
extra zeros added in front of the origial account number - some numbers have
one zero , some two and some numbers in this column are correct. This means
that I cannot just remove the first couple of numbers without the risk of
damaging the correct numbers.

I would like to be able to compare perhaps the last three or four positions
of both columns and/or be able to crop off zeros at the beginning of the
numbers. How do I do this?
--
Kind regards,

Rochelle

Comparing slightly differing columns
 

You could try something like:

=IF(RIGHT(A1,4)=RIGHT(B1,4),"same","different")

Hope this helps.

Pete

On Nov 23, 10:12 am, Rochelle
wrote:
mHi,

I need to compare two columns of account numbers that come from diferent
sources. One column has the account number correctly displayed, the other has
extra zeros added in front of the origial account number - some numbers have
one zero , some two and some numbers in this column are correct. This means
that I cannot just remove the first couple of numbers without the risk of
damaging the correct numbers.

I would like to be able to compare perhaps the last three or four positions
of both columns and/or be able to crop off zeros at the beginning of the
numbers. How do I do this?
--
Kind regards,

Rochelle

Comparing slightly differing columns
 

Hi Pete,

Thanks for your suggestion!
I tried it and noticed that it compared two cells that were directly side by
side but it did not help me to find a match in the entire second column.
Perhaps my question wasn't specific enough I think... I am comparing these
columns so that I can find a match. Problem is that my list is very long and
the probability that matching accountnumbers will be lying next to each other
(in the same row) is very small.

Perhaps if I could compare a cell in the first column with the range next
door (the entire second column) to find a match that is based on the fact
that a portion of the number matches with a portion or all of the first
column's number??

Any suggestions?

--
Kind regards,

Rochelle


"Pete_UK" wrote:

You could try something like:

=IF(RIGHT(A1,4)=RIGHT(B1,4),"same","different")

Hope this helps.

Pete

On Nov 23, 10:12 am, Rochelle
wrote:
mHi,

I need to compare two columns of account numbers that come from diferent
sources. One column has the account number correctly displayed, the other has
extra zeros added in front of the origial account number - some numbers have
one zero , some two and some numbers in this column are correct. This means
that I cannot just remove the first couple of numbers without the risk of
damaging the correct numbers.

I would like to be able to compare perhaps the last three or four positions
of both columns and/or be able to crop off zeros at the beginning of the
numbers. How do I do this?
--
Kind regards,

Rochelle

Comparing slightly differing columns
 

Say column A:A contains the correct acct#. Column B:B contains the
other set of accounts.
To test that an acct in A:A exists in column B:B with leading "0"'s,
put this formula in C1:

=SUMPRODUCT(--ISNUMBER(SEARCH(A1,$B$1:$B$100)))0

It will give you true when it finds the number. Now, for the reverse
problem, examine if the acct in B:B is coming from an acct in A:A:

=SUMPRODUCT(--ISNUMBER(SEARCH($A$1:$A$100,B1)))0

Does this help?
Kostis Vezerides

On Nov 23, 12:12 pm, Rochelle
wrote:
mHi,

I need to compare two columns of account numbers that come from diferent
sources. One column has the account number correctly displayed, the other has
extra zeros added in front of the origial account number - some numbers have
one zero , some two and some numbers in this column are correct. This means
that I cannot just remove the first couple of numbers without the risk of
damaging the correct numbers.

I would like to be able to compare perhaps the last three or four positions
of both columns and/or be able to crop off zeros at the beginning of the
numbers. How do I do this?
--
Kind regards,

Rochelle