# 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

#1
December 15, 2009 at 04:48:49
 Hi,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 exampleIn 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 \$ signsDrag 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.Regards

Report •

#2
December 16, 2009 at 15:02:11

Report •

#3
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

#4
December 17, 2009 at 06:05:31
 Hi,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.Regards

Report •