• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Data Comparison

Yulechka

New Member
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!
 

Attachments

AlanSidman

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

Code:
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"
 

Attachments

Yulechka

New Member
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

Well-Known Member
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.
 

Yulechka

New Member
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

New Member
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.
 
Top