Reconcile Two Columns BUT also Consider duplicate values

A Durfani

Board Regular
Joined
Apr 12, 2019
Messages
226
Office Version
  1. 2013
Platform
  1. 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.

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
 

Some videos you may like

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
Joined
Aug 18, 2015
Messages
10,739
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))),"")
 
Solution

A Durfani

Board Regular
Joined
Apr 12, 2019
Messages
226
Office Version
  1. 2013
Platform
  1. Windows
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
 

A Durfani

Board Regular
Joined
Apr 12, 2019
Messages
226
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Aug 18, 2015
Messages
10,739
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
Joined
Apr 12, 2019
Messages
226
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Nov 18, 2019
Messages
893
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Nov 18, 2019
Messages
893
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Oct 24, 2015
Messages
7,499
just for fun...
WSCreditWS_not_CreditCresit_not_WS
50375.78300872.04
30050793.131934
375.78251456.251804.72
793.1323.874610.767.95
1456.25872.04123.4588
251934543.2199
23.87375.78
375.7874
741804.72
4610.76315.56
315.56375.78
2929
375.787.95
123.4588
00
543.2199

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
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top