#1
|
|||
|
|||
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. |
#2
|
||||
|
||||
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 |
#3
|
|||
|
|||
Fuzzy lookup the free Microsoft add-in might be worth a try
Keith |
#4
|
|||
|
|||
=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"/"". |
|
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 |
Compare relative value with respect to specific column | PRA007 | Excel Programming | 2 | 01-26-2016 06:11 AM |
Compare 4 columns & find dissimilar | kmorrin | Excel | 4 | 06-29-2011 04:38 PM |
Compare data in different columns in an Excel sheet | kgfendi | Excel | 5 | 05-16-2009 05:42 PM |