Reconcile two columns and Find The Differences

[Sufiyan97]

I want to match A and B columns and want to find :

1. Amount in Column A but not in Column B
2. Amount in Column B But not in Column A

A	B
1​	10​
2​	11​
3​	12​
4​	14​
5​	15​
6​	16​
7​	1​
8​	2​
9​	3​
10​	21​
11​	22​
12​	23​
13​	24​
14​	25​
15​	26​
16​	27​
17​	28​
18​	29​
19​	30​
20​	31​

 

[CA_Punit]

Try This

Book1
	A	B	C	D	E	F
1	A	B			Amount in Column A But Not in Column B	Amount in Column B Not in Column A
2	1	10			4	21
3	2	11			5	22
4	3	12			6	23
5	4	14			7	24
6	5	15			8	25
7	6	16			9	26
8	7	1			13	27
9	8	2			17	28
10	9	3			18	29
11	10	21			19	30
12	11	22			20	31
13	12	23
14	13	24
15	14	25
16	15	26
17	16	27
18	17	28
19	18	29
20	19	30
21	20	31
Sheet1

Cell Formulas
Range	Formula
F2:F16	F2	=IFERROR(INDEX($B$2:$B$21,AGGREGATE(15,6,1/(1/(ISERROR(MATCH($B$2:$B$21,$A$2:$A$21,0))*(ROW($A$2:$A$21)-ROW($A$2)+1))),ROWS($F$2:F2))),"")
E2:E17	E2	=IFERROR(INDEX($A$2:$A$21,AGGREGATE(15,6,1/(1/(ISERROR(MATCH($A$2:$A$21,$B$2:$B$21,0))*(ROW($A$2:$A$21)-ROW($A$2)+1))),ROWS($E$2:E2))),"")

 

[Sufiyan97]

Try This

Book1
	A	B	C	D	E	F
1	A	B			Amount in Column A But Not in Column B	Amount in Column B Not in Column A
2	1	10			4	21
3	2	11			5	22
4	3	12			6	23
5	4	14			7	24
6	5	15			8	25
7	6	16			9	26
8	7	1			13	27
9	8	2			17	28
10	9	3			18	29
11	10	21			19	30
12	11	22			20	31
13	12	23
14	13	24
15	14	25
16	15	26
17	16	27
18	17	28
19	18	29
20	19	30
21	20	31
Sheet1

Cell Formulas
Range	Formula
F2:F16	F2	=IFERROR(INDEX($B$2:$B$21,AGGREGATE(15,6,1/(1/(ISERROR(MATCH($B$2:$B$21,$A$2:$A$21,0))*(ROW($A$2:$A$21)-ROW($A$2)+1))),ROWS($F$2:F2))),"")
E2:E17	E2	=IFERROR(INDEX($A$2:$A$21,AGGREGATE(15,6,1/(1/(ISERROR(MATCH($A$2:$A$21,$B$2:$B$21,0))*(ROW($A$2:$A$21)-ROW($A$2)+1))),ROWS($E$2:E2))),"")

Thank you very much it works, I have tried it on 20 cells, but can you please cover large range in formula so that if numbers are more for example up to 300 cells so that it will work.

 

[alansidman]

An alternative is to use Get and Transform (Power Query) found on your Data Tab.

1. Bring each column into the PQ editor as a table
2. Close and Load each to a Connection only
3. Merge the two tables as a new query with a full outer join

Results below.

Book2
	A	B
1	A	Table2.B
2	1	1
3	10	10
4	2	2
5	11	11
6	3	3
7	12	12
8	14	14
9	15	15
10	16	16
11		21
12		22
13		23
14		24
15		25
16		26
17		27
18		28
19		29
20		30
21		31
22	4
23	5
24	6
25	7
26	8
27	9
28	13
29	17
30	18
31	19
32	20
Sheet2

 

[Sufiyan97]

An alternative is to use Get and Transform (Power Query) found on your Data Tab.

1. Bring each column into the PQ editor as a table
2. Close and Load each to a Connection only
3. Merge the two tables as a new query with a full outer join

Results below.

Book2
	A	B
1	A	Table2.B
2	1	1
3	10	10
4	2	2
5	11	11
6	3	3
7	12	12
8	14	14
9	15	15
10	16	16
11		21
12		22
13		23
14		24
15		25
16		26
17		27
18		28
19		29
20		30
21		31
22	4
23	5
24	6
25	7
26	8
27	9
28	13
29	17
30	18
31	19
32	20
Sheet2

Thank you very much for your help!
Power Query is Awesome!