Data Comparison

[Yulechka]

Hello! I have two reports with the mismatched accounts in their appropriate sections. I need to compare what section is missing what accounts. Basically, it's two ways comparison. My approach was with vlookup and then highlight the missing accounts, but it is a tedious process and maybe there is more advanced approach. I am attaching the sample file. Much appreciated your insights!

 

[AlanSidman]

Using Power Query, load each table. Merge the two tables. See Mcode below and attached file for full analysis.

let
    Source = Table.NestedJoin(Table1, {"Account"}, Table2, {"Account"}, "Table2", JoinKind.FullOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"Account", "Account Number"}, {"Table2.Account", "Table2.Account Number"})
in
    #"Expanded Table2"

 

[Yulechka]

Thank you so much! I am not familiar with VB code. Could it be smth by using excel formulas, not a code? Thanks again!

 

[Peter Bartholomew]

The solution offered is not VBA. It is a Power Query solution that may be examined from the Data ribbon tab by selecting Queries and Connections.

@AlanSidman
You could also evaluate left and right antijoins for @Yulechka
= Table.NestedJoin(Table1, {"Account"}, Table2, {"Account"}, "Table2", JoinKind.RightAnti)

@Yulechka
Were you to have Office 365 Insider Channel, reasonably direct formula solutions would also be available using FILTER
= FILTER( Section2, ISERROR(MATCH(Section2[Account],Section1[Account],0)) )

This is also possible in older versions of Excel using SMALL to pack the missing records down but that is less elegant as well as being somewhat tedious.

 

[AlanSidman]

Look at the link in my signature re: Power Query

 

[AlanSidman]

@Peter Bartholomew
I concur but thought it might be easier for the OP to see all versus anti.

 

[Yulechka]

The solution offered is not VBA. It is a Power Query solution that may be examined from the Data ribbon tab by selecting Queries and Connections.

@AlanSidman
You could also evaluate left and right antijoins for @Yulechka
= Table.NestedJoin(Table1, {"Account"}, Table2, {"Account"}, "Table2", JoinKind.RightAnti)

@Yulechka
Were you to have Office 365 Insider Channel, reasonably direct formula solutions would also be available using FILTER
= FILTER( Section2, ISERROR(MATCH(Section2[Account],Section1[Account],0)) )

This is also possible in older versions of Excel using SMALL to pack the missing records down but that is less elegant as well as being somewhat tedious.

Oh, thank you! I will try to learn Power Query and use your Mcode.

 

[Yulechka]

The solution offered is not VBA. It is a Power Query solution that may be examined from the Data ribbon tab by selecting Queries and Connections.

@AlanSidman
You could also evaluate left and right antijoins for @Yulechka
= Table.NestedJoin(Table1, {"Account"}, Table2, {"Account"}, "Table2", JoinKind.RightAnti)

@Yulechka
Were you to have Office 365 Insider Channel, reasonably direct formula solutions would also be available using FILTER
= FILTER( Section2, ISERROR(MATCH(Section2[Account],Section1[Account],0)) )

This is also possible in older versions of Excel using SMALL to pack the missing records down but that is less elegant as well as being somewhat tedious.

@Peter Bartholomew Unfortunately I don't have Office 365 Insider Channel, but thank you, it's good to know.