This is a bit convoluted, so I'm not sure if there is an easy solution.
I have 2 spreadsheets with data and I want to create a 3rd sheet that only records the differences between the 2.
For example:
Sheet 1
| Column A | Column B |
|---|---|
| Lion | 5 |
| Tiger | 10 |
| Bear | 15 |
| Panda | 20 |
Sheet 2
| Column A | Column B |
|---|---|
| Lion | 5 |
| Tiger | 10 |
| Bear | 18 |
Result Sheet
| Column A | Column B |
|---|---|
| Bear | 15 |
| Panda | 20 |
The result would print out Bear because the value changed from 15 to 18, and it would print out Panda because Panda was not on the second list.
Is this something I can easily do in excel? I've looked at a few videos and other threads but can't seem to find what I'm looking for.
Here is a screenshot of what is happening for me
CodePudding user response：
Using FILTER() Function 2 ways.
• Formula can be used in cell G3
=FILTER(A3:B6,IFNA(((D3:D5=A3:A6)*(E3:E5=B3:B6))<>1,TRUE))
• Formula can be used in cell G3
=FILTER(A3:B6,COUNTIFS(D3:D5,A3:A6,E3:E5,B3:B6)=0)
I realized the 2nd approach will be much more neater than the first one, so I will explain the 2nd one before,
• COUNTIFS() function is used to get the number of times all criterias are met.
=COUNTIFS(D3:D5,A3:A6,E3:E5,B3:B6)
• We can see from above it returns 1 & 0 so the 0's are those which are not met and we need those, hence doing Boolean Check which returns TRUE & FALSE respectively.
=COUNTIFS(D3:D5,A3:A6,E3:E5,B3:B6)=0
Last not least to get the required output, wrap it within the FILTER()
=FILTER(A3:B6,COUNTIFS(D3:D5,A3:A6,E3:E5,B3:B6)=0)
A quick explanation on the 1st approach, how the formula works:
• =(D3:D5=A3:A6) the Boolean checks for whether there is a match in Column A Sheet 2 with Column A Sheet 1 and returns TRUE & FALSE & if not found returns #N/A
• =(E3:E5=B3:B6) Again, we are trying to match between Column B of both the sheets and which returns TRUE & FALSE & if not found returns #N/A respectively.
• Next, we are multiplying both the Boolean Values from the above to see whether they are returning 1 & 0 or not, where 1 signifies TRUE while 0 signifies FALSE & we are taking those which are not one's
=((D3:D5=A3:A6)*(E3:E5=B3:B6))<>1
• As the above is returning #N/A for the one which is not found, therefore we are wrapping the whole of the above within an IFNA() so whcih checks for the error and returns TRUE
=IFNA(((D3:D5=A3:A6)*(E3:E5=B3:B6))<>1,TRUE)
Finally FILTER() Function returns the array only when there is TRUE
=FILTER(A3:B6,IFNA(((D3:D5=A3:A6)*(E3:E5=B3:B6))<>1,TRUE))
For those using Excel 2010/2013/2016/2019, can try using with INDEX() & AGGREGATE()
• Formula used in cell K3
=INDEX($A$3:$B$6,AGGREGATE(15,6,
(ROW($A$3:$B$6)-ROW($A$3) 1)/
(COUNTIFS($D$3:$D$5,$A$3:$A$6,$E$3:$E$5,$B$3:$B$6)=0),ROW(A1)),0)
Note: Since I have shown in the same worksheet instead of multiple sheets you may need to change the ranges and sheet names are per your data accordingly.