Comparing slightly differing columns

[Rochelle]

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?

 

[Pete_UK]

You could try something like:

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

Hope this helps.

Pete

 

[Rochelle]

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

You could try something like:

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

Hope this helps.

Pete

 

[vezerid]

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