Pivot table question - working with difficult table layout to display results in a pivot table

buroh

Board Regular
Joined
Jul 14, 2020
Messages
52
Office Version
  1. 365
Platform
  1. Windows
Hello all,

I have a table which I have created a query so to display this information in a Pivot table
1738774760832.png


I will have other tabs with same layout, which I will combine/append later. Right now the issue is with displaying this information in a pivot table, I cannot filter by date (columns N onwards) to show the P number, because there will be 50 slicers, which is utter pointless.

The end result would be for example filter by 31/01/2025 would only show one column with the P numbers and the relevant document information linked to the filtered date & P numbers
1738774662734.png

I do not want fifty date columns in the pivot table, because I might as well use the Query sheet, so that would defeat the object of a pivot table which can grow and shrink depending on what date you filter by.

Is there another way of displaying this information without have 1000 rows of formulas which doesn't help in what I am trying to achieve which is a tidy version of the query.
Or is there another way of changing the layout of the query to suit. I did try the column transpose but it didn't work that well either.
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
You should unpivot the date columns in your query.
 
Upvote 0
You should unpivot the date columns in your query.
Thank you, but then how would I display the P numbers without the date since they are in each column with the date as the header?
 
Upvote 0
The data will be reduced to two columns - a date and the P number. You can then slice on date.
 
Upvote 0
How do I unpivot the date columns in my query?

They are all part of the same table. If you mean remove the columns in the query?
 
Upvote 0
Select all the non-date columns, then right-click a header and choose Unpivot other columns
 
Upvote 0

Forum statistics

Threads
1,226,452
Messages
6,191,132
Members
453,641
Latest member
enfkkviesm

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