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:

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.


I can think of different ways : - doing it through a macro - doing it with an intermediate table that counts if a (pair, category) appears in the table - doing it through array formulae (not evident)

As I don't know which solutions you already tried, or how advanced a user of excel you are, it's difficult to provide more precise answer without a more precise question

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.