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]

What would a sample answer look like?

 

[fordtsai]

The sample answer must be a merge of the 2 worksheets in 1 data. like combining the DAY and NIGHT shift schedule based on the machine no, commodity, date, etc.

example:

Machine: A3 (day shift) will add A3 (night shift) and become 1 day process.

 

[FDibbins]

OK, but can you show what you expect your answer to look like?

 

[fordtsai]

Hi,

This should be the final look when 2 sheets combine:

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Machine No.[/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]16800[/TD]
[TD]4 JAN[/TD]
[/TR]
</tbody>[/TABLE]








noticed that tha A3 machine added the total qty from DAY and NIGHT data.
How can I do this, I don't know what method to use or formula, please help.

 

[FDibbins]

This info is in different files, right?
What would the file names be?
Would there be other files for different dates for each shift?

 

[fordtsai]

This info is in different files, right?
What would the file names be?
Would there be other files for different dates for each shift?

1. Yes, this should be now a different file when day and night shift file combined.

2. The filenames of the files that i used are:
dayshift.xlsx
nightshift.xlsx
fullshift.xlsx (for the merged data)

3. I only have 2 files with different dates, machine no., commodity that are produced during day and night shift production.

I am confused on how to merge the 2 shifts according to their machine no, commodity and date.

 

[fordtsai]

Here is the link of the file

https://onedrive.live.com/redir?res...465&authkey=!AML6Ttn34AYA5cM&ithint=file,xlsx

 

[FDibbins]

Just so we don't get going in wrong directions...
Is the list of machines constant?
Do they match in both files A2 = machine xyz in both files?

Have you considered putting all this into 1 single file with 3 different sheets?

I am unable to access file-hosting sites from where I am right now

 

[fordtsai]

the machine no. used for production changed during day or night shift, what i really want to get is how to sum and get the total quantity of the 2 files based on the machine no. and date respectively.