Combining Columns in Pivot

[lucas257]

Hello. I am looking to identify if there is a way to combine columns within a pivot. In the attached you will see a sheet called "operator Avg Efficiency By Job" It is of course referencing data from the "master data sheet". Within a manufacturing plant we are looking to track operator efficiency by job using the operator clock number. Some jobs only require 1 operator others can require up to 4. Problem is the same operator depending on the day and the job can be in any spot within columns T-W on the master data sheet depending on how many people run the cell. I would like to figure out a way that it can grab all of the operators no matter what column they are typed into and get it to consolidate into one column in the pivot with the OEE related back to the job they were working. Let me know if this makes sense....i could probably clarify this more.

[queuesef]

well, without the example of how you would like data to look (The forum rules ask that you provide this)....Can you provide a total on the operator columns? Then run another pivot table on the total Column (you MUST exclude Ops #1-Ops #4 from Pivot table!). Perhaps try a Calculated field in Pivot table (add up the Operators?). Better examples will provide better results

[lucas257]

Hello, I've detailed in the attached highlighted in yellow what i'm attempting to accomplish. I posted on this a few weeks back and i wasn't very descriptive (my apologies on that) I have made this more descriptive on what i want this to look like. Any questions/comments i will answer to promptly. I've tried a few different attempts to get this right; i have to come to the experts.

Luke

[Pepe Le Mokko]

Administrative note

Please do not start a new thread pertaining to the same question. I added your new thread to the existing one
Have a look at Forum rule #5 about duplicate threads.
Thanks

[Mrrrr]

What do you mean "combined"?

In the case of Row 1777 for example, how would Op #1, Op #2, Op #3 and Op #4 look "combined" in the Pivot?

[lucas257]

What you have listed above for the op 1, 2, 3, 4 scenario, their numbers would have to be located in Column A of the pivot. The pivot is setup correctly it is just a matter of getting the other operator columns to sync into the "A" column of the pivot. As i am unsure what you can do in excel to merge 9 columns of like information into column A under the "rows" field of the pivot.

[Mrrrr]

As far as I know you can't show multiple columns in one in a Pivot with the way your data source looks now.

To achieve the attached Pivot (with all Operators in 1 column) I had to move all operators to column D in Data sheet and all the data that was under Op #1, Op #2 etc. in column E in Data sheet.
Of course I had to copy / paste the data in columns A-C in Data sheet for every operator, so the number or rows increased from 2092 to 6187 rows (headers excluded).

I hope somebody has a solution for your initial data source, without having to manually move operators to 1 column. I just don't know such a solution.

[lucas257]

You did the "same" exact thing i did. I think that is the only way to do it (within my capabilities). You may be able to write a quick macro that will do that exact function. but to my knowledge and yours it doesn't appear there is an easy way to do this.

[JeteMc]

I filled in the blank values under the operators with zeros. (see Data sheet)
Using the filters I found that there is a #REF error in one cell in the date column which I filled with the date above.
I also found two cells under operator 1 which had the letter K preceding the number, so removed the K
Next the range was converted to a table.
Then used Power Query (free ad-in for the 2013 version) to unpivot the Operator columns using:

Please Login or Register  to view this content.

The pivot table on the PT sheet appears to be the same as the one on the Pivot sheet, so I would assume there is no significant difference between the table on the PQTable sheet and the table on the Data2 sheet.
Let us know if you have any questions.