A Durfani
Board Regular
 Joined
 Apr 12, 2019
 Messages
 226
 Office Version

 2013
 Platform

 Windows
I have two columns A and B I want to find Number in column A but NOT in column B, Number in column B but not in column A.
I am using this formula by CA_Punit
C2: =IFERROR(INDEX($A$2:$A$17,AGGREGATE(15,6,1/(1/(ISERROR(MATCH($A$2:$A$17,$B$2:$B$17,0))*(ROW($A$2:$A$17)ROW($A$2)+1))),ROWS(B$2:$C2))),"")
Range: C2:C6
D2: =IFERROR(INDEX($B$2:$B$17,AGGREGATE(15,6,1/(1/(ISERROR(MATCH($B$2:$B$17,$A$2:$A$17,0))*(ROW($A$2:$A$17)ROW($A$2)+1))),ROWS($D$2:D2))),"")
Range: D2:D6
I am getting below result:
But I want result as below i.e. the 2 Number in column B is 2 times so it should show that 2 number is not in column A
Book1  

A  B  
1  A  B  
2  1  1  
3  2  2  
4  3  2  
5  4  3  
6  5  4  
Sheet1 
I am using this formula by CA_Punit
C2: =IFERROR(INDEX($A$2:$A$17,AGGREGATE(15,6,1/(1/(ISERROR(MATCH($A$2:$A$17,$B$2:$B$17,0))*(ROW($A$2:$A$17)ROW($A$2)+1))),ROWS(B$2:$C2))),"")
Range: C2:C6
D2: =IFERROR(INDEX($B$2:$B$17,AGGREGATE(15,6,1/(1/(ISERROR(MATCH($B$2:$B$17,$A$2:$A$17,0))*(ROW($A$2:$A$17)ROW($A$2)+1))),ROWS($D$2:D2))),"")
Range: D2:D6
I am getting below result:
Cell Formulas  

Range  Formula  
C2:C6  C2  =IFERROR(INDEX($A$2:$A$17,AGGREGATE(15,6,1/(1/(ISERROR(MATCH($A$2:$A$17,$B$2:$B$17,0))*(ROW($A$2:$A$17)ROW($A$2)+1))),ROWS(B$2:$C2))),"") 
D2:D6  D2  =IFERROR(INDEX($B$2:$B$17,AGGREGATE(15,6,1/(1/(ISERROR(MATCH($B$2:$B$17,$A$2:$A$17,0))*(ROW($A$2:$A$17)ROW($A$2)+1))),ROWS($D$2:D2))),"") 
But I want result as below i.e. the 2 Number in column B is 2 times so it should show that 2 number is not in column A
Book1  

F  G  
1  In Column A but not column B  In Column B but not column A  
2  5  2  
3  0  0  
4  0  0  
5  0  0  
6  0  0  
Sheet1 