 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / November 2005Comparing 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 You could allow for the extra spaces in your compare function: instead of a3=a2, use trim(a3)=trim(a2). > 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 [quoted text clipped - 31 lines] > JSSU0611339 > JSSU0618924 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 -- > 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 [quoted text clipped - 31 lines] > JSSU0611339 > JSSU0618924 And a nice closure note received from the OP .. To: "Max" <demechanik@yahoo.com> Subject: Re: 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 -- Check http://www.j-walk.com/ss/excel/usertips/tip027.htm OR http://www.j-walk.com/ss/excel/usertips/tip048.htm  SignatureRani > 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 [quoted text clipped - 31 lines] > JSSU0611339 > JSSU0618924 |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.