Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Slightly OT (Maybe) printing problem | Excel Worksheet Functions | |||
Printout slightly smaller than preview | Excel Discussion (Misc queries) | |||
comparing two columns | Excel Discussion (Misc queries) | |||
Comparing two columns of information with 2 new columns of informa | Excel Discussion (Misc queries) | |||
excel charting formulas - sum of 2 columns with differing data | Charts and Charting in Excel |