Creating a master list from many lists

[btillis94]

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.

[#2]

Try Power Query

[#3]

@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.

[#4]

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 

[#5]

hi @Riny_van_Eekelen - i think i need the same function. but does your formula mean that if i move the file to another location i need to update the code? this code depends on the file staying in one place on the server?

[#6]

@redriverbluehill Not sure I follow, but if you connect to a file with Power Query to a file like in the example file that was attached to the original post, you select one of the sheets to transform. Then, in the applied steps list Source and Navigation will show, where Source has the correct path the file on your server. Delete the Navigation step so that you are left with the content of the entire workbook.

Now you can filter for all the sheets you need/want from the Kind column, and expand the Data column. And that's basically what the code in my answer does.