Creating a master list from many lists

%3CLINGO-SUB%20id%3D%22lingo-sub-2588337%22%20slang%3D%22en-US%22%3ECreating%20a%20master%20list%20from%20many%20lists%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2588337%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20document%20with%2024%20tabs%2C%20each%20has%20different%20sales%20data%20about%20products.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20want%20to%20combine%20all%20of%20these%20products%20into%20one%20master%20list%2C%20with%20the%20duplicates%20removed.%20I%20only%20want%20to%20make%20the%20list%20consist%20of%20columns%20%22(Parent)%20ASIN%22%20and%20%22Title%22%20(columns%20A%20and%20B%20in%20the%20sheets).%3CBR%20%2F%3E%3CBR%20%2F%3EI%20understand%20I%20could%20copy%20paste%20and%20remove%20duplicates%2C%20but%20I%20want%20to%20be%20able%20to%20plug%20in%20any%2024%20sheets%20(we%20do%20this%20for%20many%20companies)%20and%20have%20a%20formula%20that%20automatically%20creates%20a%20master%20list.%20What%20is%20the%20best%20way%20to%20do%20this%3F%3CBR%20%2F%3E%3CBR%20%2F%3EI%20was%20told%20by%20Microsoft%20support%20that%20this%20might%20entail%20using%20PIVOT%20or%20a%20master%20list%20function%3F%20Open%20to%20any%20and%20all%20solutions.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2588337%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2588462%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20a%20master%20list%20from%20many%20lists%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2588462%22%20slang%3D%22en-US%22%3ETry%20Power%20Query%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2589637%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20a%20master%20list%20from%20many%20lists%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2589637%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1113065%22%20target%3D%22_blank%22%3E%40btillis94%3C%2FA%3E%26nbsp%3BAnd%20this%20is%20how.%20(Workbook%20with%20PQ%20attached)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEdit%3A%20Uploaded%20the%20wrong%20file%20initially.%20Then%20realized%20that%20it%20connected%20to%20a%20file%20on%20my%20own%20system.%20Better%20to%20upload%20the%20M-code%20that%20you%20can%20paste%20into%20the%20Advanced%20Editor.%20Can't%20add%20it%20here%2C%20so%20refer%20to%20my%20next%20post.%20Just%20change%20the%20path%20name%20to%20the%20file.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

I have a document with 24 tabs, each has different sales data about products.

I want to combine all of these products into one master list, with the duplicates removed. I only want to make the list consist of columns "(Parent) ASIN" and "Title" (columns A and B in the sheets).

I understand I could copy paste and remove duplicates, but I want to be able to plug in any 24 sheets (we do this for many companies) and have a formula that automatically creates a master list. What is the best way to do this?

I was told by Microsoft support that this might entail using PIVOT or a master list function? Open to any and all solutions.

3 Replies
Try Power Query

@btillis94 And this is how. (Workbook with PQ attached)

 

Edit: Uploaded the wrong file initially. Then realized that it connected to a file on my own system. Better to upload the M-code that you can paste into the Advanced Editor. Can't add it here, so refer to my next post. Just change the path name to the file.

 

 

 

let
    Source = Excel.Workbook(File.Contents(".............\Desktop\Summary2.xlsx"), true, true),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Item] <> "Summary") and ([Kind] = "Sheet")),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Data"}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Removed Other Columns", "Data", {"(Parent) ASIN", "Title"}, {"(Parent) ASIN", "Title"}),
    #"Removed Duplicates" = Table.Distinct(#"Expanded Data", {"(Parent) ASIN"}),
    #"Sorted Rows" = Table.Sort(#"Removed Duplicates",{{"(Parent) ASIN", Order.Ascending}})
in
    #"Sorted Rows"

@btillis94