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

 

[fordtsai]

hi, i tried to get the link

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

and i don't know why you can not access?

 

[fordtsai]

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

yes itried putting all these into 1 file with 2 different sheets, but i really dont know how to calculate or come up with a formula that will get the total quantity of both day and night shift data based on machine no and date respectively.

 

[FDibbins]

EWould it be possible to use a helper column in each data file, to create a unique ID for each machine/date?

 

[fordtsai]

yes please, how can I do the correct calculation with this helper column?

 

[FDibbins]

hi, i tried to get the link

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

and i don't know why you can not access?

I am not allowed to go there, from work

 

[fordtsai]

how can i make this helper column? unique ID? and use this to combine the 2 sheets?

 

[FDibbins]

In both the Day and Night shift files, insert a new column A (your data will now start in column B)
In that column, A2 (assuming that is the row your data starts)...
A2=IF(B2="","",B2&E2)
copied down.
Do this for both files.

Then in your All shift file (not sure if this is what you want, but it's a start)...
[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]B2[/td][td][/td][td]0[/td][td]4-Jan[/td][/tr]

[tr][td]

16​

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

[tr][td]

17​

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

[tr][td]

18​

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

[tr][td]

19​

[/td][td]C3[/td][td][/td][td]0[/td][td]4-Jan[/td][/tr]
[/table]
Column A contains a list of all your machines, and column D contains the same date for each machine. The machine list is then repeated for the next day.
B2=IFERROR(VLOOKUP($A2&$D2,[dayshift.xlsx]Sheet1!$A$2:$D$100,3,0),"")
2=IFERROR(VLOOKUP($A2&$D2,[dayshift.xlsx]Sheet1!$A$2:$D$100,4,0),0)+IFERROR(VLOOKUP($A2&$D2,[nightshift.xlsx]Sheet1!$A$2:$D$100,4,0),0)
both copied down as needed

 

[fordtsai]

A2=IF(B2="","",B2&E2)

In your formula, what item list in cell E2?

and for this formula:
B2=IFERROR(VLOOKUP($A2&$D2,[dayshift.xlsx]Sheet1!$A$2:$D$100,3,0),"")
2=IFERROR(VLOOKUP($A2&$D2,[dayshift.xlsx]Sheet1!$A$2:$D$100,4,0),0)+IFERROR(VLOOKUP($A2&$D2,[nightshift.xlsx]Sheet1!$A$2:$D$100,4,0),0)

what cell is cell 2? is this C2 column?

 

[fordtsai]

is this possible for pivot table?

 

[FDibbins]

In your formula, what item list in cell E2?

Remember, we inserted a new column A, which pushes all the other columns over. E is now your date column (was column D)

2=IFERROR(VLOOKUP($A2&$D2,[dayshift.xlsx]Sheet1!$A$2:$D$100,4,0),0)+IFERROR(VLOOKUP($A2&$D2,[nightshift.xlsx]Sheet1!$A$2:$D$100,4,0),0)

Apologies, that was meant to be C2= not 2=