1

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.

1 Answer 1

0

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .