I am looking for a solution for creating a pivot table for a data table where multiple columns may contain the same type of keyword or tag in Excel 2010. I have searched for an answer but have not yet come up with anything.
I have attached an example with a number of kids, their age, gender and their favourite foods with some questions that I would like my pivot table to answer. As you can see from the example the kids may like one or more types of foods, and each type of food can appear in a number of different columns. I'd like my pivot table to be able to answer questions like:
I know that my example questions can be answered quite easily without using a pivot table, but my real data set is much more complex than this example in terms of numbers of rows and number of other relevant columns. Therefore I'd be very happy if someone can come up with a pivot table solution to this problem.Number of kids who like Ice Cream?
Number of kids who like Ice Cream and Hot Dogs?
Number of kids who like more than one type of food?
Favourite foods in terms of number of likes per food item?
Favourite foods ordered by gender of kids?
What is the average age of kids who like Ice Cream?
Bookmarks