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

 

[alansidman]

Here is the updated file Copy of Query Table.xlsx | Powered by Box

I duplicated the original source file and then merged the two files (Join) based on the common field. Expanded the table to show only fields that would not be duplicated.

 

[DocRogers]

Thank you for all the help. On the columns you are adding back in to the Mcode, they don't need to be merged as the data in them is the same, for example in the original column E "Vendor on Order" that information is the same for all locations, so if 500 is on order its only on order once even if 4 locations have the same quantity it wouldn't be 2000 only 500. This is the same for columns C to K. Columns A, AA, AB, and AC are not needed. I hope I made sense on this one. Again thank you so much for the help.

 

[DocRogers]

Here is the updated file Copy of Query Table.xlsx | Powered by Box

I duplicated the original source file and then merged the two files (Join) based on the common field. Expanded the table to show only fields that would not be duplicated.

 

[DocRogers]

Thank you, I will give it a go and let you know how it comes out. Thank you so much for the help. Have a great weekend.

 

[DocRogers]

It worked sort of though with an error. I'm pasting below 15 lines that i did manually as this would be what i need my finished output to be. If you compare that with the original files first 5 lines the part was sold to 5 of the 6 locations. As you can see below, I've merged the 5 into 1 line and summed columns L through Y but just copied the others. I also don't need column A, or any of them past Y. Does this make sense? Thank you

Locations

Item

VEND ON ORD

CUST ON ORD

ON HAND

MinStockingQty

Val@AvgCost

BACK ORDER

IN PICKING

QTY SOLD 12 MNTH

QTY SOLD MTD

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

DSR

#9 PVC SHRINK FILM BAGS

0​

190​

2273​

2000​

210.2525​

0​

3​

2203​

24​

77​

105​

111​

79​

261​

116​

18​

198​

318​

214​

194​

488​

dib

0701FBC18802125260EVM

0​

0​

0​

0​

0​

0​

0​

7​

0​

0​

7​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

DSR

0701FBC18802500260EVM

0​

0​

2​

1​

58.1428​

0​

0​

4​

0​

0​

1​

0​

0​

0​

0​

0​

1​

0​

0​

2​

0​

DSR

0701FBC18803375260EVM

0​

2​

4​

2​

227.658​

0​

0​

13​

0​

1​

0​

0​

1​

0​

1​

3​

0​

0​

0​

7​

0​

DSR

0701FBC18804500260EVM

0​

0​

33​

4​

2053.3524​

0​

0​

23​

0​

0​

1​

1​

1​

1​

2​

0​

2​

2​

10​

3​

0​

DSR

0701FBC18804500260EVM

0​

0​

33​

4​

2053.3524​

0​

0​

23​

0​

0​

1​

1​

1​

1​

2​

0​

2​

2​

10​

3​

0​

DSR

0701FBC18805375260EVM

0​

1​

29​

4​

2039.6193​

0​

0​

14​

0​

2​

0​

0​

0​

0​

1​

0​

4​

2​

2​

3​

0​

DSR

0701FBN18801000260EH

0​

0​

14​

1​

202.5856​

0​

0​

11​

0​

0​

0​

0​

5​

0​

0​

4​

0​

0​

0​

2​

0​

bah

07-2088

0​

0​

3​

0​

16.71​

0​

0​

57​

0​

20​

0​

0​

13​

6​

0​

6​

2​

0​

0​

10​

0​

bah

07-2158

0​

0​

0​

0​

0​

0​

0​

12​

0​

2​

0​

0​

0​

0​

0​

3​

0​

0​

0​

5​

2​

bah

07-2165

0​

0​

0​

0​

0​

0​

0​

2​

0​

0​

0​

0​

0​

0​

0​

2​

0​

0​

0​

0​

0​

bah

07-2168

0​

0​

0​

0​

0​

0​

0​

1​

0​

0​

0​

0​

0​

0​

0​

1​

0​

0​

0​

0​

0​

bah

101-059-02

0​

0​

235​

0​

4905.813​

0​

0​

87​

0​

0​

0​

0​

0​

0​

0​

9​

0​

41​

37​

0​

0​

DSR

102-016-02

50​

17​

34​

0​

907.4022​

0​

0​

84​

0​

20​

24​

0​

0​

0​

4​

6​

10​

2​

18​

0​

0​

bah

103-112-01

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

 

[alansidman]

Not enough information. This comment doesn't help me to help you. You should not be pasting data. You should be merging the file back onto itself otherwise you lose the automation of PQ.

It worked sort of though with an error

 

[DocRogers]

I'm not sure what you mean by merging the file back onto itself? When I look at the new file you created, i see a merge1 tab, Power_Query2 tab which is blank, Power_query which looks like my original file but with the lines merged and then Power Query which i assume is my original file. Is the Merge1 tab just a copy as it looks like the original data. Also the Power_Query tab seems correct, is this the finished page with all duplicate lines merged? Thank you again for your help.

 

[alansidman]

In the left window, right click on the Query created and duplicate it. In the duplicated query, delete all steps except source. Now you have the original to join/merge onto the existing query.