HELP! Display text in pivot table

Hi All

I am new and hoping you might be able to assist. I am trying to display text in a pivot table (or something that looks like a pivot table) so it is easier to understand. Can anyone help? File attached. Crew A.xlsx
the original data is in col A to I.
I am trying to achieve something like whats in col K. A single list of names in col K. A list of all the capabilities (currenlty in col F) displayed across the top row and in the body i would like to see the level. So it would only display the level if the name had the capability.


Additionaly i would like to be able to swap the value from level to expires at a later date.

Hope someone can help.
Thanks
Rae

Crew A rv1.xlsx
I'm not quite sure if you can get a pivot table to do that. They are meant to summarize data. I managed to get the pivot table to show the data the ability was obtained or whether it was obtained. For showing the level, I used the index and match functions. If you don't want the #N/A you can remove them by using select ->special->logical errors, or you could edit the formula to include a conditional IF(ISERROR()).

Hope this helps.

Hi, thanks for this. It took me a little while just to figue out how you built this but i've got it now Thanks its perfect!
Rae

Welcome to the Forum Rae,
Please check the attached if you want it in pivot table format.
Best Regards/VKS

You're welcome. I learned about index and match on the ExcelIsFun youtube channel, and you can do a lot with them. Also, I used to use concatenate until I found that you can just use &. Glad I could help.

VKS - good idea to add capability as a row value.

k64