Help with setting up a pivot table

Hello everybody, today I discovered the pivot tables. So I decided to give it a try. Now I have encountered a problem which I cannot solve. I have looked everywhere for an answer but could not find it. So I guess the problem lays in the way I set up the table.
Attached please find the table I am working on. What I am trying to achieve is the following:
When I select a collection I need to see which artefacts are part of it (maybe ordered by lvl), the xp they give, the amount of chronos they give and... here comes the problem: I only need to see which materials are needed for that specific collection, the columns that have no values should be hidden. I also include a manually generated example of the required outcome.

Thanks for any help you can provide

Hi there,
See if the Pivot table on the workbook is what you are after.
I have renamed the first 3 materials; removed the 'Sum of'. Note, the name can't be the name of the table heading, so I left the 'space' in front. You could make some other difference..

Hi ORoos, yes, this is what I am looking for. Just one last question: if for some artefatct I don't need some materials, is it possible NOT to show the column?

Hi s7yzrs,

Don't think you can do that with the actual pivot table. But a simple VBA code can do it.
I entered the code into Sheet1 to trigger each time the pivot table is updated (a new selectin is made).

Please Login or Register  to view this content.

I have also added a 'Slicer' in column A. This makes the selection of your Collection(s) a bit easier.

Wow, I am impressed. I had no idea that pivot tables could do this much. Thank you for your help. I will analyze what you sent me and try to understand it.
Again, thank you very much indeed.

I just wish I could find the "solved" button to close the topic....

Glad I wsa of some help.

To mark your thread solved do the following:
New Method
-Go to the top of the first post
-Select Thread Tools
-Select Mark thread as Solved

Old Method
- Go to the first post
- Click edit
- Click Advance
- Just below the word "Title:" you will see a dropdown with the word No prefix.
- Change to Solve
- Click Save

Great.... thank you again