Same labels/Fields in multiple columns

Jaeger88

New Member
Joined
Nov 25, 2016
Messages
9
Dear helper,

The following is a fragment from a table. The table shows routings with required work centers. What would be the easiest way to make an overview of volume (KG) per workcenter in monthly buckets (Date)?


DateKGRoutingWC1WC2WC3WC4WC5WC6WC7
1/16/20171.667TOFSPD002SE1RIDRPR1BR1BB1/TT 1DRPR1ASL1
1/8/20176632CACSP004SZ1DG1
DR1SP
3/10/20170.52LTLSPD002CD1ASL1ICL1ICL1DR1NRSP
3/10/2017
0.499LTLSPD002
CD1ASL1ICL1ICL1DR1NRSP
1/14/2017262.761WMWSP001ASL1HA1SP

<colgroup><col span="3"><col><col span="6"></colgroup><tbody>
</tbody>


Help is much appreciated!

Warm regards
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Hi Roderick,

What selection you need to create such an overview. Because when I select:

Column labels: Date (Group to month)
Values: Sum of KG
Row labels: WC1,..WC7 (all workcenters)

I get an incorrect overview because it puts the WC2 under the 1 and so forward.

Thanks in advance!

Capture.png
 
Upvote 0
Ah sorry jumped in too quickly, I see now you don't have just one work center column. I was looking at routing column. However, since you only have one KGs column how would you know which goes with which work center?
 
Upvote 0
The goal is to have an overview of total workload for each separate workcenter in monthly buckets.

1 row in the starting table resembles a production order, with volume, routing and date, and based on the routing the work centers are vlooked up (WC1-WC7). Now for a monthly overview of the load of a workcenter there is the need sum up all the volume of all the workcenters. Given the fact that workcenters go into different routings and in these routings the workcenter come into a different sequence the starting table looks how it looks. And just to be clear the volume (kg) goes over all the work centers in each row.

Example workcenter ASL1 shows in row 1 and row 5 for a total volume in january of 264.428, now I look for a way to retrieve this nr automatically which I cant because its in differents columns (WC1 & WC7).

I hope you can support me

Thanks
 
Last edited:
Upvote 0
The goal is to have an overview of total workload for each separate workcenter in monthly buckets.

1 row in the starting table resembles a production order, with volume, routing and date, and based on the routing the work centers are vlooked up (WC1-WC7). Now for a monthly overview of the load of a workcenter there is the need sum up all the volume of all the workcenters. Given the fact that workcenters go into different routings and in these routings the workcenter come into a different sequence the starting table looks how it looks. And just to be clear the volume (kg) goes over all the work centers in each row.

Example workcenter ASL1 shows in row 1 and row 5 for a total volume in january of 264.428, now I look for a way to retrieve this nr automatically which I cant because its in differents columns (WC1 & WC7).

I hope you can support me

Thanks

I accomplished this by using a series of SUMIFS and a helper column that gave me the YEAR and month. This assumes the sample data is in column A-J and headers on row 1. My YEAR and month code is in column K and dragged down. Then my result code is in cell R2, with 2017-01 in P2 and ASL1 in cell Q2.

YEAR-MONTH CODE
Code:
=YEAR(A2)&"-"&TEXT(MONTH(A2),"00")
RESULT CODE
Code:
=SUMIFS(B:B,K:K,P2,D:D,Q2)+SUMIFS(B:B,K:K,P2,E:E,Q2)+SUMIFS(B:B,K:K,P2,F:F,Q2)+SUMIFS(B:B,K:K,P2,G:G,Q2)+SUMIFS(B:B,K:K,P2,H:H,Q2)+SUMIFS(B:B,K:K,P2,I:I,Q2)+SUMIFS(B:B,K:K,P2,J:J,Q2)
 
Upvote 0
This works and i can reshuffle the formulas to get a nice table with months in the columns and work centers in rows. Much appreciated!

Thanks very much!!
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,923
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top