Forum Discussion
Automatically remove empty columns in a pivot table
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 Name | Product name | Proces Step | Name 1 | Name 2 | Name 3 | Name 4 | Name 5 | Name 6 | Name 7 | Name 8 | Name 9 | Name 10 |
| Line A | ABC | Proces step 1 | 3 | 1 | 1 | |||||||
| Line A | ABC | Proces step 2 | 3 | 1 | 1 | |||||||
| Line A | ABC | Proces step 3 | 1 | 1 | 3 | 1 | 0 | 0 | 3 | 1 | ||
| Line A | ABC | Proces step 4 | 1 | 1 | 3 | 1 | 0 | 0 | 3 | 1 | ||
| Line A | ABC | Proces step 5 | 3 | 1 | 3 | 1 | 0 | 0 | 3 | 1 | ||
| Line A | ABC | Proces step 6 | 3 | 1 | 3 | 1 | 0 | 0 | 3 | 1 | ||
| Line A | ABC | Proces step 7 | 3 | 1 | 3 | 1 | 0 | 0 | 3 | 1 | ||
| Line A | ABC | Proces step 8 | 3 | 1 | 3 | 1 | 0 | 0 | 3 | 1 | ||
| Line A | ABC | Proces step 9 | 3 | 1 | 3 | 1 | 0 | 0 | 3 | 1 | ||
| Line B | DEF | Proces step 1 | 3 | 1 | 1 | 3 | 1 | 1 | 3 | |||
| Line B | DEF | Proces step 2 | 4 | 3 | 1 | 4 | 3 | 1 | 4 | |||
| Line B | DEF | Proces step 3 | 3 | 3 | 3 | 3 | ||||||
| Line B | DEF | Proces step 4 | 4 | 3 | 4 | 3 | ||||||
| Line B | DEF | Proces step 5 | 4 | 4 | ||||||||
| Line C | GHI | Proces step 1 | 4 | 3 | 1 | 4 | 3 | 1 | 4 | 3 | ||
| Line C | GHI | Proces step 2 | 3 | 1 | 1 | 3 | 1 | 1 | 3 | |||
| Line C | GHI | Proces step 3 | 4 | 3 | 1 | 4 | 3 | 1 | 4 | |||
| Line C | GHI | Proces step 4 | 3 | 3 | 3 | 3 | ||||||
| Line C | GHI | Proces step 5 | 4 | 3 | 4 | 3 |
So as you can see, Name 3 is not trained for Line A Product name ABC. My Pivot table is as below:
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!!
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.
3 Replies
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 🙂Yannick_Smeets Check out this link. It's good start.
