comparison - Comparing Multiple Lists of Data in Excel to find gaps

Question

I am conducting a product analysis and have data on all the products my company has and need to compare that against all the products that our competitors have. I need to find out where the gaps are, in particular what products do they have that we don't? from the multiple lists of data I have on each competitors products lists against our product data list. What is the best formula to use or way to find and interpret this data in Excel? Thanks

question from:https://stackoverflow.com/questions/65884188/comparing-multiple-lists-of-data-in-excel-to-find-gaps

Answer 1

Join the tables using Power query.

• Table 1 = your company

• key = Product

• Table2 through TableN= competitors

• key = Product

• Combine the competitors tables into a single table.

• Do a nestedjoin with JoinKind.RightAnti which will return all the products in Table 2 that do not exist in Table 1

• We use a Nested join since the keys have the same Column Header

M Code You can paste this code into the Power Query Advanced Editor, and change the Name= argument in lines 2 through N to reflect your actual table names

If you have many competitors, it is possible to create a function to gather all the table names, but overkill for just a few

Step through Applied Steps in the Power Query UI to see what each line does.

let
    myCompany = Excel.CurrentWorkbook(){[Name="myCompany"]}[Content],
    otherCompany = Excel.CurrentWorkbook(){[Name="otherCompany"]}[Content],
    company3 = Excel.CurrentWorkbook(){[Name="company3"]}[Content],

//Join the competitor tables
    competitors = Table.Combine({otherCompany,company3}),

//finde the missing
    missing = Table.NestedJoin(myCompany,"Product",competitors,"Product", "Missing", JoinKind.RightAnti),
    #"Removed Columns" = Table.RemoveColumns(missing,{"Product", "Description"}),
    #"Expanded Missing" = Table.ExpandTableColumn(#"Removed Columns", "Missing", {"Product", "Description"}, {"Missing.Product", "Missing.Description"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Missing",{{"Missing.Product", Order.Ascending}})
    
in
    #"Sorted Rows"