SOLVED

Automatically remove empty columns in a pivot table

New Contributor

Hi ‎all‎,

 

I have a database for my operator skills define per line and product. Out of this I made a pivot table to sort them as I want.

but sometimes I have operators which are not trained for that specific line, so I get an empty column in my pivot table.

 

How can I sort the table in such a way that the empty columns automatically dissapear? 

 

Below an example of the database

Line NameProduct nameProces StepName 1Name 2Name 3Name 4Name 5Name 6Name 7Name 8Name 9Name 10
Line AABCProces step 13   1   1 
Line AABCProces step 23   1   1 
Line AABCProces step 311  310031
Line AABCProces step 411  310031
Line AABCProces step 531  310031
Line AABCProces step 631  310031
Line AABCProces step 731  310031
Line AABCProces step 831  310031
Line AABCProces step 931  310031
Line BDEFProces step 1 311 311 3
Line BDEFProces step 2 431 431 4
Line BDEFProces step 3  33  33  
Line BDEFProces step 4  43  43  
Line BDEFProces step 5   4   4  
Line CGHIProces step 1431 431 43
Line CGHIProces step 2 311 311 3
Line CGHIProces step 3 431 431 4
Line CGHIProces step 4  33  33  
Line CGHIProces step 5  43  43  

 

 

So as you can see, Name 3 is not trained for Line A Product name ABC. My Pivot table is as below:

 

Yannick_Smeets_0-1623243769092.png

 

I dont want each time to switch the persons on and off when I select another line.

The goal is to have the empty's out of the table.

If possible it would be great if there is a solution without VBA, because I'm not familiar with that.

 

PS and while I'm asking, is there also a way to get the "Sum of" in the name automatically gone?

 

thanks!!

3 Replies
best response confirmed by Yannick_Smeets (New Contributor)
Solution

@Yannick_Smeets See attached! On the tab called "pt" I recreated the pivot table as shown in your picture. Below it, another one based on the same, but unpivoted, data. Used Power Query to transform your data, load it to the Data Model and then create the second pivot table how you want it. Added a sheet "unpivoted" to demonstrate what that means.

Thanks for the quick respons and the answer Riny. this is exactly what I needed

the only challenge now is that I'm not familiar with Power Query so I have something new to learn