Reconcile Two Columns BUT also Consider duplicate values

[Sufiyan97]

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.

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:

Book1
	C	D
1	In Column A but not column B	In Column B but not column A
2	5	0
3	0	0
4	0	0
5	0	0
6	0	0
Sheet1

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

 

[Sufiyan97]

sandy666​

just for fun...

WS	Credit		WS_not_Credit		Cresit_not_WS
50	375.78		300		872.04
300	50		793.13		1934
375.78	25		1456.25		1804.72
793.13	23.87		4610.76		7.95
1456.25	872.04		123.45		88
25	1934		543.21		99
23.87	375.78
375.78	74
74	1804.72
4610.76	315.56
315.56	375.78
29	29
375.78	7.95
123.45	88
0	0
543.21	99

// WS_not_Credit
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    AB = List.Difference(Source[WS], Source[Credit])
in
    AB

// Cresit_not_WS
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    BA = List.Difference(Source[Credit], Source[WS])
in
    BA

Thank you! sandy666
This also works perfectly but formula is easier to work with.

 

[sandy666]

This also works perfectly but formula is easier to work with.

sure, no problem, thanks for the feedback

 

[Sufiyan97]

sure, no problem, thanks for the feedback

Thank you again for your time and giving perfect solution.

 

[sandy666]

anytime in spare time

 

[Sufiyan97]

just for fun...

WS	Credit		WS_not_Credit		Cresit_not_WS
50	375.78		300		872.04
300	50		793.13		1934
375.78	25		1456.25		1804.72
793.13	23.87		4610.76		7.95
1456.25	872.04		123.45		88
25	1934		543.21		99
23.87	375.78
375.78	74
74	1804.72
4610.76	315.56
315.56	375.78
29	29
375.78	7.95
123.45	88
0	0
543.21	99

// WS_not_Credit
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    AB = List.Difference(Source[WS], Source[Credit])
in
    AB

// Cresit_not_WS
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    BA = List.Difference(Source[Credit], Source[WS])
in
    BA

Hello sandy666

When I was working with above formula with 7K+ rows Exel gets freeze but your solution with power query works very well and not freezing sheet. Now I understand the power of power query.

Thank you very much.

 

[sandy666]

Hello sandy666

When I was working with above formula with 7K+ rows Exel gets freeze but your solution with power query works very well and not freezing sheet. Now I understand the power of power query.

Thank you very much.

You are very welcome

 

[Eric W]

Try:

Book1
	A	B	C	D
1	Withdrawal Statement	Credit	In Column A but not in column B	In column B but not in column A
2	50	375.78	300	872.04
3	300	50	793.13	1934
4	375.78	25	1456.25	1804.72
5	793.13	23.87	4610.76	7.95
6	1456.25	872.04	123.45	88
7	25	1934	543.21	99
8	23.87	375.78
9	375.78	74
10	74	1804.72
11	4610.76	315.56
12	315.56	375.78
13	29	29
14	375.78	7.95
15
16	123.45	88
17	0	0
18	543.21	99
19
Sheet12

Cell Formulas
Range	Formula
C2:C14	C2	=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($A$2:$A$500)/($A$2:$A$500>0)/(COUNTIF(OFFSET($A$1,0,0,ROW($A$2:$A$500)-ROW($A$2)+2),$A$2:$A$500)>COUNTIF($B$2:$B$500,$A$2:$A$500)),ROWS($C$2:$C2))),"")
D2:D14	D2	=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW($B$2:$B$500)/($B$2:$B$500>0)/(COUNTIF(OFFSET($B$1,0,0,ROW($B$2:$B$500)-ROW($B$2)+2),$B$2:$B$500)>COUNTIF($A$2:$A$500,$B$2:$B$500)),ROWS($D$2:$D2))),"")

The formula is pretty tricky, and I don't think I have time to explain in full how it works, but I'll try to give the gist of it. You may want to do a search on the AGGREGATE function, which is key to the whole thing.

We start out with an array of all the rows we want to look at:

=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($A$2:$A$500)/($A$2:$A$500>0)/(COUNTIF(OFFSET($A$1,0,0,ROW($A$2:$A$500)-ROW($A$2)+2),$A$2:$A$500)>COUNTIF($B$2:$B$500,$A$2:$A$500)),ROWS($C$2:$C2))),"")

This is represented internally like {2,3,4,5,6,...}. Next we want to exclude rows that have zero values, like row 15 and 17. The part in blue:

=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($A$2:$A$500)/($A$2:$A$500>0)/(COUNTIF(OFFSET($A$1,0,0,ROW($A$2:$A$500)-ROW($A$2)+2),$A$2:$A$500)>COUNTIF($B$2:$B$500,$A$2:$A$500)),ROWS($C$2:$C2))),"")

checks each cell in the range and returns TRUE or FALSE, which creates another array like this {TRUE,TRUE,TRUE,TRUE,FALSE,TRUE,...}. Then we take each individual element in the first array, and divide it by the corresponding element in the next array, and TRUE is equivalent to 1, and FALSE is equivalent to 0, so we'd end up with something like: {2,3,#DIV/0!,5,6,#DIV/0!,8...}.

The next bit is tricky. Consider this example:

Book1 (version 1).xlsb
	A	B	C	D	E	F	G	H	I	J	K
1	Withdrawal Statement	Credit
2	5	3		COUNTIF(A2:A10,A2)	3		COUNTIF(B2:B10,A2)	1		D2>G2?	TRUE
3	4	4		COUNTIF(A3:A10,A3)	1		COUNTIF(B2:B10,A2)	1		D3>G3?	FALSE
4	5	5		COUNTIF(A4:A10,A4)	2		COUNTIF(B2:B10,A2)	1		D4>G4?	TRUE
5	5	6		COUNTIF(A5:A10,A5)	1		COUNTIF(B2:B10,A2)	1		D5>G5?	FALSE
Sheet17

5 occurs 3 times in column A, and once in column B, so we want to return 2 values of 5. So for each row, we calculate the D and G formulas, and make the comparison in column J, to get the TRUE/FALSE result in column K. Note in column D the range changes with each row, but the range is constant in column G. For rows 2 and 4 we get TRUE, so we would end up outputting 2 values of 5. The K column is converted into an array like before, and it's divided into the previous array, so we might get a few more #DIV/0! values: {2,3,#DIV/0!,#DIV/0!,6,#DIV/0!,...}.

You can see the COUNTIF() > COUNTIF() within the formula. Note that the first COUNTIF has to use OFFSET to generate the variable ranges for each row.

Finally, the ostensible main function of AGGREGATE kicks in. Given an array {2,3,#DIV/0!,#DIV/0!,6,#DIV/0!,...}, find the nth SMALLest value (the 15 in AGGREGATE), ignoring errors (the 6 value). So this would return row numbers 2, 3, and 6. The final ROWS($C$2:$C2) is used to generate the counter n for 1, 2, 3, etc.

Hope this helps!

 

[Sufiyan97]

The formula is pretty tricky, and I don't think I have time to explain in full how it works, but I'll try to give the gist of it. You may want to do a search on the AGGREGATE function, which is key to the whole thing.

We start out with an array of all the rows we want to look at:

=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($A$2:$A$500)/($A$2:$A$500>0)/(COUNTIF(OFFSET($A$1,0,0,ROW($A$2:$A$500)-ROW($A$2)+2),$A$2:$A$500)>COUNTIF($B$2:$B$500,$A$2:$A$500)),ROWS($C$2:$C2))),"")

This is represented internally like {2,3,4,5,6,...}. Next we want to exclude rows that have zero values, like row 15 and 17. The part in blue:

=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($A$2:$A$500)/($A$2:$A$500>0)/(COUNTIF(OFFSET($A$1,0,0,ROW($A$2:$A$500)-ROW($A$2)+2),$A$2:$A$500)>COUNTIF($B$2:$B$500,$A$2:$A$500)),ROWS($C$2:$C2))),"")

checks each cell in the range and returns TRUE or FALSE, which creates another array like this {TRUE,TRUE,TRUE,TRUE,FALSE,TRUE,...}. Then we take each individual element in the first array, and divide it by the corresponding element in the next array, and TRUE is equivalent to 1, and FALSE is equivalent to 0, so we'd end up with something like: {2,3,#DIV/0!,5,6,#DIV/0!,8...}.

The next bit is tricky. Consider this example:

Book1 (version 1).xlsb
	A	B	C	D	E	F	G	H	I	J	K
1	Withdrawal Statement	Credit
2	5	3		COUNTIF(A2:A10,A2)	3		COUNTIF(B2:B10,A2)	1		D2>G2?	TRUE
3	4	4		COUNTIF(A3:A10,A3)	1		COUNTIF(B2:B10,A2)	1		D3>G3?	FALSE
4	5	5		COUNTIF(A4:A10,A4)	2		COUNTIF(B2:B10,A2)	1		D4>G4?	TRUE
5	5	6		COUNTIF(A5:A10,A5)	1		COUNTIF(B2:B10,A2)	1		D5>G5?	FALSE
Sheet17

5 occurs 3 times in column A, and once in column B, so we want to return 2 values of 5. So for each row, we calculate the D and G formulas, and make the comparison in column J, to get the TRUE/FALSE result in column K. Note in column D the range changes with each row, but the range is constant in column G. For rows 2 and 4 we get TRUE, so we would end up outputting 2 values of 5. The K column is converted into an array like before, and it's divided into the previous array, so we might get a few more #DIV/0! values: {2,3,#DIV/0!,#DIV/0!,6,#DIV/0!,...}.

You can see the COUNTIF() > COUNTIF() within the formula. Note that the first COUNTIF has to use OFFSET to generate the variable ranges for each row.

Finally, the ostensible main function of AGGREGATE kicks in. Given an array {2,3,#DIV/0!,#DIV/0!,6,#DIV/0!,...}, find the nth SMALLest value (the 15 in AGGREGATE), ignoring errors (the 6 value). So this would return row numbers 2, 3, and 6. The final ROWS($C$2:$C2) is used to generate the counter n for 1, 2, 3, etc.

Hope this helps!

Thank you very much Eric!

I need to go through your explanation step by step because I am trying to decode formula since you have provided it in October 2020 but not getting it.

Thank you very much again for your time and explanation!