Compare data in 2 columns in MS Excel

Microsoft Excel 2003 (full product)
December 14, 2009 at 17:23:04
Specs: Windows XP
Here is my problem: In Column A I have a list of 50 Invoice numbers that I show unpaid. In Column B I have the invoice numbers showing paid, however there is an additional set of numbers ranging from 8 to 12 in front of the numbers I need to match.

Column A-- 43689149 (50 different numbers)
Column B--0015083702 42710914 (6900 different numbers)

In essence I want to compare each value in A (in this example 43689149) and see if it matches the latter portion of column B, all 6900 numbers, ( in this example 42710914).

Any Suggestions??

See More: Compare data in 2 columns in MS Excel

Report •

December 15, 2009 at 04:48:49

Try the following:
Add a column to the right of Column A.
The 'long' paid numbers are now in column C - starting in C2 in my example
In column D alongside the first long number (D2 in my example), enter this formula:

=RIGHT(C2,LEN(C2)-FIND(" ",C2,1))
Drag this formula down alongside all of the long numbers. This will give you a list of the shorter numbers, ready for comparison.
In B2 enter this formula:
=IF(ISERROR(VLOOKUP(TEXT(A2,"#"),$D$2:$D$12,1,FALSE)),"Not found","Found")
Note the $ signs
Drag the formula down alongside the short numbers.

The TEXT(A2,"#") part of the formula is because I am assuming the values in column A are numbers and not text. The values in column D that they are being compared to, are text.


Report •

December 16, 2009 at 15:02:11
43689149 Not found 0011037140 43762353 43762353
43699441 Not found 0030204655 42827287 42827287
43753635 Not found 6800157733 42872276 42872276
43762353 Not found 1967619840 42911547 42911547
43762672 Not found 0060477569 42913459 42913459

Heres the first few rows of my table and as you can see A4 is stating not found when it actually sits in D1. The ren command with find worked great and I had actually found that too, but came up with the same issue as you. Any tweaks? Thank you!

Report •

December 16, 2009 at 18:39:17
I found a solution:

I copied the information you had me put into cell D and pasted it to cell E but as a paste special I then chose value only. After it copied it over I chose to convert it to numbers instead of text and then changed the formula to look for the array inside column E instead of D. Thanks so much for your help!!!

Report •

Related Solutions

December 17, 2009 at 06:05:31

Glad it worked.

A couple of hints in case you do more Excel work like this:

1. =IF(ISERROR(VLOOKUP(A2,$D$2:$D$12,1,FALSE)),"Not found","Found")
Using this formula assumes that the data being tested is text. The original formula converted the data in column A from a number to text.
2.=VALUE(RIGHT(C2,LEN(C2)-FIND(" ",C2,1)))
This version of the formula that separated out the 'short' number, converts the text to a number.
3. Sometimes comparing text doesn't work because of spaces around the text - which you can't see. So this version =IF(ISERROR(VLOOKUP(TRIM(A2),$D$2:$D$12,1,FALSE)),"Not found","Found") uses the TRIM function to remove any spaces.
4. When comparing or looking up numbers, there may be minor differences between numbers way out in the last few decimal places, (which don't show in the cell), so use the ROUND() function to round both numbers down to fewer decimal places, and the minor differences will disappear.


Report •

Ask Question