This is driving me nuts. I feel like I'm missing the obvious. Help would be wonderful.
I have a set of data in an Excel spreadsheet that looks like the below:
PRODUCT CATEGORY
fork utensil
fork stabby-thing
spoon utensil
spoon bowlish
knife utensil
knife stabby-thing
knife not-safe-for-kids
My goal is to create a crosstab chart that tallies instances where two categories overlap for a given product.
CATEGORIES bowlish not-safe-for-kids stabby-thing utensil
bowlish - 0 0 1
not-safe-for-kids 0 - 1 1
stabby-thing 0 1 - 2
utsensil 1 1 2 -
If the above isn't clear: I want to tally how many times the categories coexist within products. Another way would be to say I want the number of 'overlaps'. Categories stabby-thing and utensil coexist on two separate products, so there's a value of 2 where they cross-section in the chart. No product is both bowlish and not-safe for things, so its value is 0. And so on.
I have Excel and Access, and I'm sure there's some refined or simple way to do this, but I'm missing it.
Thanks so much for any help.