Extracting non-matching entries from two columns in a third column
I have come across a thought, that if there is a way to do an automatic reconciliation via excel. It is simply that i want to compare two columns with each other and extract non-matching entries within them. Meaning that for each record in one column, only one record in another column must match. An example will more clarify this case:
| Non-Matching Records | |||
| A | B | Records that are in A, but not in B | Records that are in B, but not in A |
| 1000 | 1000 | ||
| 1000 | 5000 | ||
| 2000 | 17000 | ||
| 5000 | 3000 | ||
| 3000 | |||
| -1000 | |||
| 202.31 | |||
I want excel to look up in these two columns and match for each record in column B that is in column A and if they do match skip the matching records and only return non-matching entries in their respective columns (i.e. column 3 & 4). I very urgently need help in this regard. I would really appreciate if somebody end up with an answer and be helpful.
If further explanation is needed, plz let me know.
Is this what you are looking for?
1. Check values in column A against column B on your sample data:
=IF(ISNUMBER(MATCH(A1;$B$1:$B$4;0))=FALSE;"In A, not in B";"")
2. Check values in column B against column A on your sample data:
=IF(ISNUMBER(MATCH(B1;$A$1:$A$7;0))=FALSE;"In B, not in A";"")
3. If you need only rows where both columns don't match, we will combine these two formulas in a single AND formula and look for TRUE only.
No it is not the result i want
Well first i thank you for responding. But let me make it more obvious.
This is the result i want:
Excel must match for each record in column B, only one record that is in column A and extract the non-matching records as per the result in Column 3 & 4. I have also attached the excel file if you want to do the working directly in the file.