Merging and adding multiple sheets in a workbook

[DocRogers]

Hello, I have a workbook that has 6 sheets. The sheets are shipping locations for one customer and have all the parts that location has bought as well as the pertinent information for the part, ie.. quantity, cost, how many sold in each of the last 12 months and so on. I need to combine all the sheets into one and I need to merge all the duplicate parts and add the totals in each column with each other. For example part ABC, 100 pcs on each tab, i only want to show the part# once and add the 100pcs from each sheet into 1 total of 600. I have attached a picture of the first 6 rows as well as the headers. All sheets have the same headers. I hope I explained this clearly. Thank you

 

[Skybluekid]

Hi

What version of excel are you using? It would be useful to everyone know what version you are using.

If you are able to use it Power Query may be an option. As you can combine all sheets and then do the manipulation before it spits it out.

 

[DocRogers]

Hi, sorry I didn't think of including the version. I'm using Office 365
Microsoft® Excel® for Microsoft 365 MSO (Version 2301 Build 16.0.16026.20002) 32-bit

 

[Skybluekid]

So Power Query would be an option

 

[DocRogers]

unfortunately I'm not familiar with it. I have done some look up but it seems that its just merging all the sheets to one sheet. I need do that but i also need to then have it merge the duplicate part numbers into a single row but it also needs to add the numbers in those rows to get a single part number with a total quantity shipped for each month. I created the power query but still can't figure out how to make it do the rest.

 

[alansidman]

You are asking for some detailed help that requires a bit more than you are currently offering. It would be really helpful if you uploaded some sample files with 8-10 records each to a third party like Box.net or Dropbox.com. Additionally, in a separate file, show what the expected results you wish mocked up for the records in the sample files. We'll get you there and this will help big time.

 

[DocRogers]

ok, i've uploaded a sample file to drop box. Sample file.xlsx
I've also uploaded the power query just incase you wanted to see what i had already done. Power Query

 

[alansidman]

Here is the Mcode to sum the sales by part number

let
    Source = Excel.CurrentWorkbook(){[Name="Power_Query"]}[Content],
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Item", "QTY SOLD 1 MO AGO", "QTY SOLD 2 MO AGO", "QTY SOLD 3 MO AGO", "QTY SOLD 4 MO AGO", "QTY SOLD 5 MO AGO", "QTY SOLD 6 MO AGO", "QTY SOLD 7 MO AGO", "QTY SOLD 8 MO AGO", "QTY SOLD 9 MO AGO", "QTY SOLD 10 MO AGO", "QTY SOLD 11 MO AGO", "QTY SOLD 12 MO AGO"}),
    #"Grouped Rows" = Table.Group(#"Removed Other Columns", {"Item"}, {{"1 Month", each List.Sum([QTY SOLD 1 MO AGO]), type number}, {"2 Month", each List.Sum([QTY SOLD 2 MO AGO]), type number}, {"3 Month", each List.Sum([QTY SOLD 3 MO AGO]), type number}, {"4 Month", each List.Sum([QTY SOLD 4 MO AGO]), type number}, {"5 Month", each List.Sum([QTY SOLD 5 MO AGO]), type number}, {"6 Month", each List.Sum([QTY SOLD 6 MO AGO]), type number}, {"7 Month", each List.Sum([QTY SOLD 7 MO AGO]), type number}, {"8 Month", each List.Sum([QTY SOLD 8 MO AGO]), type number}, {"9 Month", each List.Sum([QTY SOLD 9 MO AGO]), type number}, {"10 Month", each List.Sum([QTY SOLD 10 MO AGO]), type number}, {"11 Month", each List.Sum([QTY SOLD 11 MO AGO]), type number}, {"12 Month", each List.Sum([QTY SOLD 12 MO AGO]), type number}})
in
    #"Grouped Rows"

See the file here-->Copy of Query Table.xlsx | Powered by Box

 

[DocRogers]

I'll give that a try, thank you. I do want to keep all the columns, will this get rid of them like your example?

 

[alansidman]

I will need to reconstruct to show all columns. This will require that you merge the query back onto its original self. I will post an update once I have completed that.