Combining 2 field columns and sum total quantity from different worksheets

[fordtsai]

Hi,

I'm currently working on a project with a data that has day and night shift using excel 2013, i tried combining these 2 worksheet using powerpivot but no luck because I don't know really what method to use to accomplish my goal:

1. Merge 2 sheets and sum the total qty of both day and night shift data as per machine no., commodity, date to become a 1 day process?

File 1: (Day Shift)

[TABLE="width: 500"]
<tbody>[TR]
[TD]Machine[/TD]
[TD]Commodity[/TD]
[TD]Total[/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD]C1[/TD]
[TD]UFP HOUSING[/TD]
[TD]1040[/TD]
[TD]3 JAN[/TD]
[/TR]
[TR]
[TD]B14[/TD]
[TD]HUBBLE[/TD]
[TD]15600[/TD]
[TD]4 JAN[/TD]
[/TR]
[TR]
[TD]A3[/TD]
[TD]BIG BEAR[/TD]
[TD]15400[/TD]
[TD]4 JAN[/TD]
[/TR]
</tbody>[/TABLE]

File 2: (Night Shift)

[TABLE="width: 500"]
<tbody>[TR]
[TD]Machine No.[/TD]
[TD]Commodity[/TD]
[TD]Total[/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD]A3[/TD]
[TD]BIG BEAR[/TD]
[TD]1400[/TD]
[TD]4 JAN[/TD]
[/TR]
[TR]
[TD]A5[/TD]
[TD]AIR SPOILER[/TD]
[TD]8840[/TD]
[TD]4 JAN[/TD]
[/TR]
[TR]
[TD]C10[/TD]
[TD]MFP DEVICE[/TD]
[TD]13550[/TD]
[TD]4 JAN[/TD]
[/TR]
</tbody>[/TABLE]

Please help.


Ford

 

[FDibbins]

If you put Day shift and night shift on their own tab/worksheet in 1 file, then you could have a 3rd sheet to do the summary...
[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][/tr]
[tr][td]

1​

[/td][td][/td][td]Machine[/td][td]Commodity[/td][td]Total[/td][td]Date[/td][/tr]

[tr][td]

2​

[/td][td]C142372[/td][td]C1[/td][td]UFP HOUSING[/td][td]1040[/td][td]3-Jan[/td][/tr]

[tr][td]

3​

[/td][td]B1442373[/td][td]B14[/td][td]HUBBLE[/td][td]15600[/td][td]4-Jan[/td][/tr]

[tr][td]

4​

[/td][td]A342373[/td][td]A3[/td][td]BIG BEAR[/td][td]15400[/td][td]4-Jan[/td][/tr]

[tr][td]

5​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

6​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

7​

[/td][td]Day Shift Sheet[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]
A2=IF(B2="","",B2&E2)
copied down

[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][/tr]
[tr][td]

1​

[/td][td][/td][td]Machine No.[/td][td]Commodity[/td][td]Total[/td][td]Date[/td][/tr]

[tr][td]

2​

[/td][td]A342373[/td][td]A3[/td][td]BIG BEAR[/td][td]1400[/td][td]4-Jan[/td][/tr]

[tr][td]

3​

[/td][td]A542373[/td][td]A5[/td][td]AIR SPOILER[/td][td]8840[/td][td]4-Jan[/td][/tr]

[tr][td]

4​

[/td][td]C1042373[/td][td]C10[/td][td]MFP DEVICE[/td][td]13550[/td][td]4-Jan[/td][/tr]

[tr][td]

5​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

6​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

7​

[/td][td]Night Shift Sheet[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]
A2=IF(B2="","",B2&E2)
copied down

[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][/tr]
[tr][td]

1​

[/td][td]Machine No.[/td][td]Commodity[/td][td]Total[/td][td]Date[/td][/tr]

[tr][td]

2​

[/td][td]A1[/td][td][/td][td]0[/td][td]3-Jan[/td][/tr]

[tr][td]

3​

[/td][td]A2[/td][td][/td][td]0[/td][td]3-Jan[/td][/tr]

[tr][td]

4​

[/td][td]A3[/td][td][/td][td]0[/td][td]3-Jan[/td][/tr]

[tr][td]

5​

[/td][td]B1[/td][td][/td][td]0[/td][td]3-Jan[/td][/tr]

[tr][td]

6​

[/td][td]B2[/td][td][/td][td]0[/td][td]3-Jan[/td][/tr]

[tr][td]

7​

[/td][td]B3[/td][td][/td][td]0[/td][td]3-Jan[/td][/tr]

[tr][td]

8​

[/td][td]C1[/td][td]UFP HOUSING[/td][td]1040[/td][td]3-Jan[/td][/tr]

[tr][td]

9​

[/td][td]C2[/td][td][/td][td]0[/td][td]3-Jan[/td][/tr]

[tr][td]

10​

[/td][td]C3[/td][td][/td][td]0[/td][td]3-Jan[/td][/tr]

[tr][td]

11​

[/td][td]A1[/td][td][/td][td]0[/td][td]4-Jan[/td][/tr]

[tr][td]

12​

[/td][td]A2[/td][td][/td][td]0[/td][td]4-Jan[/td][/tr]

[tr][td]

13​

[/td][td]A3[/td][td]BIG BEAR[/td][td]16800[/td][td]4-Jan[/td][/tr]

[tr][td]

14​

[/td][td]B1[/td][td][/td][td]0[/td][td]4-Jan[/td][/tr]

[tr][td]

15​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

16​

[/td][td]Full shift sheet[/td][td][/td][td][/td][td][/td][/tr]
[/table]

B2=IFERROR(VLOOKUP($A2&$D2,dayshift!$A$2:$D$100,3,0),"")
C2=IFERROR(VLOOKUP($A2&$D2,dayshift!$A$2:$D$100,4,0),0)+IFERROR(VLOOKUP($A2&$D2,Nightshift!$A$2:$D$100,4,0),0)

(from 1 Ford to another Ford :0 )

 

[fordtsai]

Thanks for the solution you have shown Ford, I will try this one..