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,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
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)
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
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: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
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

Other Forums: Access Forums

All times are GMT -7. The time now is 07:35 AM.


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