# Reconcile Two Columns BUT also Consider duplicate values

#### A Durfani

##### Board Regular
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
AB
1AB
211
322
432
543
654
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
CD
1In Column A but not column BIn Column B but not column A
250
300
400
500
600
Sheet1
Cell Formulas
RangeFormula
C2:C6C2=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:D6D2=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
FG
1In Column A but not column BIn Column B but not column A
252
300
400
500
600
Sheet1

### Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

#### Eric W

##### MrExcel MVP
Try:

Book1
ABCDE
1ABIn A but not BIn B but not A
21152
322
432
543
654
Sheet13
Cell Formulas
RangeFormula
D2:D6D2=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:E6E2=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))),"")

#### A Durfani

##### Board Regular
Try:

Book1
ABCDE
1ABIn A but not BIn B but not A
21152
322
432
543
654
Sheet13
Cell Formulas
RangeFormula
D2:D6D2=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:E6E2=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

##### MrExcel MVP
Happy to help! Thanks for the update.

#### A Durfani

##### Board Regular

Try:

Book1
ABCDE
1ABIn A but not BIn B but not A
21152
322
432
543
654
Sheet13
Cell Formulas
RangeFormula
D2:D6D2=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:E6E2=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
ABCD
1Withdrawal StatementCreditIn Column A but not in column BIn column B but not in column A
250375.78300872.04
330050.00793.131934
4375.7825.001456.251804.72
5793.1323.874610.760
61456.25872.046077.383292
7251,934.004791.572785.38
823.87375.784126.80
9375.7874.002178.060
10741,804.72 0
114610.76315.56 0
12315.56375.78 0
132929.00 1200
14375.787.95 0
Sheet1
Cell Formulas
RangeFormula
C2:C14C2=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:D14D2=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))),"")

Last edited:

#### Eric W

##### MrExcel MVP
Try:

Book1
ABCD
1Withdrawal StatementCreditIn Column A but not in column BIn column B but not in column A
250375.78300872.04
330050793.131934
4375.78251456.251804.72
5793.1323.874610.767.95
61456.25872.04123.4588
7251934543.2199
823.87375.78
9375.7874
10741804.72
114610.76315.56
12315.56375.78
132929
14375.787.95
15
16123.4588
1700
18543.2199
19
Sheet12
Cell Formulas
RangeFormula
C2:C14C2=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:D14D2=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))),"")

#### A Durfani

##### Board Regular

Try:

Book1
ABCD
1Withdrawal StatementCreditIn Column A but not in column BIn column B but not in column A
250375.78300872.04
330050793.131934
4375.78251456.251804.72
5793.1323.874610.767.95
61456.25872.04123.4588
7251934543.2199
823.87375.78
9375.7874
10741804.72
114610.76315.56
12315.56375.78
132929
14375.787.95
15
16123.4588
1700
18543.2199
19
Sheet12
Cell Formulas
RangeFormula
C2:C14C2=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:D14D2=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

##### Well-known Member
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

##### Well-known Member
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

##### Banned - Rules violations
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``````

Replies
17
Views
306
Replies
2
Views
104
Replies
10
Views
119
Replies
1
Views
103
Replies
13
Views
141

1,126,965
Messages
5,621,871
Members
415,862
Latest member
nascaline

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back