|
Comparing 2 columns if they are the same
I have two columns to compare. I first sort them out in the same order and
then use the IF function. The If function is OK but when the rows to compare are not match due to the error in typing or spaces then there is no match. Is there another way or function to use that instead of sorting, it will just lookup the two columns and compare and then indicate that there are say 5 mismatched and indicate them? Appreciate any help JSSU0611220 JSSU0611220 JSSU0618924 JSSU0611339 JSSU0617893 JSSU0612227 JSSU0615170 JSSU 0614195 JSSU0614370 JSSU0614322 JSSU0614322 JSSU0614370 JSSU0614195 JSSU0615170 JSSU0612227 JSSU0617893 JSSU0611339 JSSU0618924 |
Comparing 2 columns if they are the same
You could allow for the extra spaces in your compare function: instead of
a3=a2, use trim(a3)=trim(a2). "Faio" wrote: I have two columns to compare. I first sort them out in the same order and then use the IF function. The If function is OK but when the rows to compare are not match due to the error in typing or spaces then there is no match. Is there another way or function to use that instead of sorting, it will just lookup the two columns and compare and then indicate that there are say 5 mismatched and indicate them? Appreciate any help JSSU0611220 JSSU0611220 JSSU0618924 JSSU0611339 JSSU0617893 JSSU0612227 JSSU0615170 JSSU 0614195 JSSU0614370 JSSU0614322 JSSU0614322 JSSU0614370 JSSU0614195 JSSU0615170 JSSU0612227 JSSU0617893 JSSU0611339 JSSU0618924 |
Comparing 2 columns if they are the same
One way
Assuming data is in cols A & B, from row2 down Put in C2: =IF(A2="","",ISNUMBER(MATCH(A2,B:B,0))) Copy down till last row of data in col A (Above compares col A against col B) Put in D2: =IF(B2="","",ISNUMBER(MATCH(B2,A:A,0))) Copy down till last row of data in col B (Above compares col B against col A) Put 2 labels into C1:D1, and do an autofilter on cols C & D in turn Filtering out: FALSE in each case (col C, then col D) would then locate the mismatches for the comparison -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Faio" wrote in message ... I have two columns to compare. I first sort them out in the same order and then use the IF function. The If function is OK but when the rows to compare are not match due to the error in typing or spaces then there is no match. Is there another way or function to use that instead of sorting, it will just lookup the two columns and compare and then indicate that there are say 5 mismatched and indicate them? Appreciate any help JSSU0611220 JSSU0611220 JSSU0618924 JSSU0611339 JSSU0617893 JSSU0612227 JSSU0615170 JSSU 0614195 JSSU0614370 JSSU0614322 JSSU0614322 JSSU0614370 JSSU0614195 JSSU0615170 JSSU0612227 JSSU0617893 JSSU0611339 JSSU0618924 |
Comparing 2 columns if they are the same
And a nice closure note received from the OP ..
To: "Max" Subject: Comparing 2 columns if they are the same Date: Tue, 15 Nov 2005 14:30:45 +1200 Thanx a lot. It works as expected. Great -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
Comparing 2 columns if they are the same
Check http://www.j-walk.com/ss/excel/usertips/tip027.htm
OR http://www.j-walk.com/ss/excel/usertips/tip048.htm -- Rani "Faio" wrote: I have two columns to compare. I first sort them out in the same order and then use the IF function. The If function is OK but when the rows to compare are not match due to the error in typing or spaces then there is no match. Is there another way or function to use that instead of sorting, it will just lookup the two columns and compare and then indicate that there are say 5 mismatched and indicate them? Appreciate any help JSSU0611220 JSSU0611220 JSSU0618924 JSSU0611339 JSSU0617893 JSSU0612227 JSSU0615170 JSSU 0614195 JSSU0614370 JSSU0614322 JSSU0614322 JSSU0614370 JSSU0614195 JSSU0615170 JSSU0612227 JSSU0617893 JSSU0611339 JSSU0618924 |
| All times are GMT +1. The time now is 06:49 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com