Pivot table to split product combinations by age and gender

[Glayva]

I have a large data set which indicates the gender and age band of customers and what products they bought.

I need to easily present this to show the breakdown of purchase baskets by gender and age band - for example, the number of women in the 35 to 44 age band who bought a bounty and a lion bar and the number of men in the 25 to 34 age band who bought all four.

I have been playing about with pivot tables but can't seem to get what I am looking for. I attach an example datasheet with an indication of the results I am looking for. Any info you can provide would be great.

Many thanks

G

[oeldere]

I re-arange the data with the macro below.

After that a pivot table.

See the attached file.

Please Login or Register  to view this content.

[Glayva]

Thanks a lot for replying, do you think a macro is the only way to resolve this issue?

[oeldere]

No, it can also be done with formula's.

But since you got al lot off (empty) columns that will be a hell of a job (at least for me).


What is the problem using a macro to help you re-arange your data?

The pivot table is the way you want it?

[Glayva]

Macro's are just a bit outside my skill set. If it helps the empty columns have no purpose so they can be deleted. Thanks again.

[oeldere]

as you have seen did I added all products in one column and all values in one column.

in that way it is the easied format to use the pivot table.


The pivot table is the way you want it?