Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-22-2017, 11:46 PM
norasiken norasiken is offline excel - to compare few column and find the different Windows XP excel - to compare few column and find the different Office 2010 32bit
Novice
excel - to compare few column and find the different
 
Join Date: Aug 2017
Posts: 1
norasiken is on a distinguished road
Default excel - to compare few column and find the different

Hi. I really need help to solve this problem.

I have few thousand data in 4 columns as below. Is there's anyway I can use excel formula to find the differences.

If ID1 match with ID2, than COURSE1 must match COURSE2.
If COURSE1 not match with COURSE2, than it will return 'error' in ERROR column.

Let say for ID1 .... 111 is abc, but in course2 its listed jkl...than error message will appeared.

ID1.........course1..........ID2............course 2
111.........abc................444............jkl
222.........def................555............abc
333.........ghi................111............jkl
444.........jkl.................222............def


555.........abc................333...........ghi

My friend suggest to do sorting and check the difference row by row. But, it will take so much time to check for thousands of data.

Hope you understand my explanation. Thanks in advanced.
Reply With Quote
  #2  
Old 08-23-2017, 04:50 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline excel - to compare few column and find the different Windows 7 64bit excel - to compare few column and find the different Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,398
Pecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of light
Default

Perhaps enter in E2
=IF(INDEX($D2:$D100,MATCH($a2,$c2:$C100,0))=$b2,"" ,"error")
Adapt range as needed and pull down as needed ( or double-click the handle)
__________________
Please note I am not active on this forum anymore
Reply With Quote
  #3  
Old 08-28-2017, 11:40 AM
kalees kalees is offline excel - to compare few column and find the different Windows 7 64bit excel - to compare few column and find the different Office 2016
Novice
 
Join Date: Feb 2017
Location: UK
Posts: 3
kalees is on a distinguished road
Default

Fuzzy lookup the free Microsoft add-in might be worth a try

Keith
Reply With Quote
  #4  
Old 08-28-2017, 10:36 PM
ArviLaanemets ArviLaanemets is offline excel - to compare few column and find the different Windows 8 excel - to compare few column and find the different Office 2016
Expert
 
Join Date: May 2017
Posts: 549
ArviLaanemets is a jewel in the roughArviLaanemets is a jewel in the roughArviLaanemets is a jewel in the rough
Default

=AND($A2=$C2,$B2<>$D2)
or when you have your datarange defined as table (and column headers are without spaces)
=AND([@ID1]=[@ID2],[@Course1]<>[@Course2])
entered into ERROR column and copied down, returns TRUE/FALSE in ERROR column. It is enough to use ERROR column for filtering/order, but of-course you can use this formula as condition in IF() function to return anything different like "error"/"".
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to compare 2 Excel sheets for 100+ matches? dylansmith Excel 5 05-22-2017 09:09 PM
Compare filenames listed in Excel to external folder. Chayes Excel Programming 3 04-02-2016 02:17 PM
excel - to compare few column and find the different Compare relative value with respect to specific column PRA007 Excel Programming 2 01-26-2016 06:11 AM
excel - to compare few column and find the different Compare 4 columns & find dissimilar kmorrin Excel 4 06-29-2011 04:38 PM
excel - to compare few column and find the different Compare data in different columns in an Excel sheet kgfendi Excel 5 05-16-2009 05:42 PM


All times are GMT -7. The time now is 12:13 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2020, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2020 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft