Reconcile Two Columns BUT also Consider duplicate values

Sufiyan97 · Oct 9, 2020

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

3

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

3

4

5

6

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

4

0

5

0

6

0

Sheet1

Eric W · Oct 9, 2020

Try:

Book1

A

B

C

D

E

1

A

B

In A but not B

In B but not A

2

1

5

2

3

2

4

3

2

5

4

3

6

5

4

Sheet13

Cell Formulas
Range	Formula
D2:D6	D2	=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($A$2:$A$6)/(COUNTIF(OFFSET($A$1,0,0,ROW($A$2:$A$6)-ROW($A$2)+2),$A$2:$A$6)>COUNTIF($B$2:$B$6,$A$2:$A$6)),ROWS($D$2:$D2))),"")
E2:E6	E2	=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW($B$2:$B$6)/(COUNTIF(OFFSET($B$1,0,0,ROW($B$2:$B$6)-ROW($B$2)+2),$B$2:$B$6)>COUNTIF($A$2:$A$6,$B$2:$B$6)),ROWS($E$2:$E2))),"")

Sufiyan97 · Oct 9, 2020

Eric W said:
Try:

Book1
A B C D E
1 A B In A but not B In B but not A
2 1 1 5 2
3 2 2
4 3 2
5 4 3
6 5 4
Sheet13
Cell Formulas
Range Formula
D2:D6 D2 =IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($A$2:$A$6)/(COUNTIF(OFFSET($A$1,0,0,ROW($A$2:$A$6)-ROW($A$2)+2),$A$2:$A$6)>COUNTIF($B$2:$B$6,$A$2:$A$6)),ROWS($D$2:$D2))),"")
E2:E6 E2 =IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW($B$2:$B$6)/(COUNTIF(OFFSET($B$1,0,0,ROW($B$2:$B$6)-ROW($B$2)+2),$B$2:$B$6)>COUNTIF($A$2:$A$6,$B$2:$B$6)),ROWS($E$2:$E2))),"")

Fantastic! This is what I was searching for Thank, you very much!
Eric W

Eric W · Oct 9, 2020

Happy to help! Thanks for the update.

Sufiyan97 · Oct 21, 2020

Eric W said:
Try:

Book1
A B C D E
1 A B In A but not B In B but not A
2 1 1 5 2
3 2 2
4 3 2
5 4 3
6 5 4
Sheet13
Cell Formulas
Range Formula
D2:D6 D2 =IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($A$2:$A$6)/(COUNTIF(OFFSET($A$1,0,0,ROW($A$2:$A$6)-ROW($A$2)+2),$A$2:$A$6)>COUNTIF($B$2:$B$6,$A$2:$A$6)),ROWS($D$2:$D2))),"")
E2:E6 E2 =IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW($B$2:$B$6)/(COUNTIF(OFFSET($B$1,0,0,ROW($B$2:$B$6)-ROW($B$2)+2),$B$2:$B$6)>COUNTIF($A$2:$A$6,$B$2:$B$6)),ROWS($E$2:$E2))),"")

Hello Eric W I am using your formula successfully every time but for now it's showing 0 instead of taking values up side for example in below range

if you can see what is it after number 2785.38 there are five zeros and after there is a number 1200 is it possible to skip zeros and get number 1200 immediately after number 2785.38?

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.00

793.13

1934

4

375.78

25.00

1456.25

1804.72

5

793.13

23.87

4610.76

0

6

1456.25

872.04

6077.38

3292

7

25

1,934.00

4791.57

2785.38

8

23.87

375.78

4126.8

0

9

375.78

74.00

2178.06

0

10

74

1,804.72

0

11

4610.76

315.56

0

12

315.56

375.78

0

13

29

29.00

1200

14

375.78

7.95

0

Sheet1

Cell Formulas
Range	Formula
C2:C14	C2	=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($A$2:$A$500)/(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)/(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))),"")

Eric W · Oct 23, 2020

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

14

375.78

7.95

15

16

123.45

88

17

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))),"")

Sufiyan97 · Oct 24, 2020

Eric W said:
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))),"")

It is working perfectly. Thank you very much!

Eric W

Your formula helps me very well and saving my time.

Thank you again!

CA_Punit · Oct 24, 2020

Hi @Eric W

I have a doubt with this part of formula =COUNTIF(OFFSET($A$1,0,0,ROW($A$2:$A$500)-ROW($A$2)+2),$A$2:$A$500)

How is this working.
Offset will return different set of Height each time.

CA_Punit · Oct 24, 2020

Got it Thanks

I thought Countif works with only Single Range multiple crieria.
Learned a new thing today with Multiple range and single criteria.

sandy666 · Oct 24, 2020

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

Power Query:

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

Power Query:

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

Reconcile Two Columns BUT also Consider duplicate values

Sufiyan97

Well-known Member

Excel Facts

Eric W

MrExcel MVP

Sufiyan97

Well-known Member

Eric W

MrExcel MVP

Sufiyan97

Well-known Member

Eric W

MrExcel MVP

Sufiyan97

Well-known Member

Eric W

CA_Punit

Well-known Member

CA_Punit

Well-known Member

sandy666

Banned - Rules violations

Similar threads

Forum statistics

Share this page

Reconcile Two Columns BUT also Consider duplicate values

Well-known Member

Excel Facts

MrExcel MVP

Well-known Member

MrExcel MVP

Well-known Member

MrExcel MVP

Well-known Member

Eric W​

Well-known Member

Well-known Member

Banned - Rules violations

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock

Eric W