Sean Morris's Portfolio
  • Portfolio
  • About
  • Blog

Custom Lambda Function to Reconcile Two Lists

5/1/2024

0 Comments

 
Say you have two lists - one from Aaron, and another from Zora. You want a formula that tells you which items are in which lists so that you can follow up with both parties to get them back on the same page. 

At least, as of right now, Excel doesn't have an easy way to do this, so I wrote a custom Lambda function to do it myself. Here's a simplified example in action: ​
Picture
The function produces an array (from a #SPILL formula) that includes a couple of useful elements:
  • headers for clarity
  • a "Distinct" column that shows all of the unique (non-duplicated) values 
  • two columns with Boolean values (1 or 0) indicating whether or not the unique item is in the given list
  • a "Category" column that codes the given item into one of 3 categories. Generically: 
    • 1. List 1 Only
    • 2. List 2 Only
    • 3. Both Lists
This way, you can easily target the discrepancies between the two lists (the Category 1s and 2s) or the commonalities (Category 3). 

If you just want to use this Lambda, here's the code you can copy and paste into the "Refers To" field in your name manager: 
Lambda To Paste Into "Refers To" Field of Name Manager

    
Description to Paste Into "Comment" Field of Name Manager

    
Picture

More Information

Special thanks to Excel MVP Owen Price at Flex Your Data for the inspiration and base method for this formula from his Simple Frequency Table (Owen is an Excel MVP and it's obvious why because his posts are awesome).

I have given more descriptive names to my LET variables (e.g. what Owen calls "X" I call "list1LogicalMatrix"), but his matrix multiplication method is the core feature - otherwise I am simply rearranging the data produced by it. For a great explanation of how this works, I recommend visiting Owen's Simple Frequency Table post. 
Full Lambda Formula (Including Parameters at the End)

    
Good luck! Happy to hear feedback / comments! 
0 Comments



Leave a Reply.

    Archives

    September 2024
    May 2024
    November 2020

    Categories

    All
    Agile Development
    Custom Excel Lambda Functions
    Data Science

    RSS Feed

    View my profile on LinkedIn
© 2024 by Sean Morris. All rights reserved. 
Portfolio |  About