How to model tagged data in Excel

November 7, 2008 8:32 AM

What's the best way to represent tagged data in Excel? I want to be able to give an item multiple tags. I would like to use these tags to generate pivot tables. But I'd rather not have to create a new column for each tag (at least, I'd rather not have to create them manually).

This problem comes up for me very frequently. Here's trivial example:

WORD     LENGTH     TAGS
drum        4         toy, instrument
hawk        4         animal, bird
cat         3         animal, mammal, pet
bear        4         animal, mammal, toy
orange      6         color, food, fruit
apple       5         food, fruit

Oh, and I'm using Excel on the Mac, and a pretty old version at that. So I'd want a solution that doesn't rely on macros or scripting.

posted by alms to technology (6 comments total)

I want to unscrew the battery compartment on my watch, to change the battery. How can I do that with a hammer?

You want a a database for this, with three two tables: dictionary (id int not null primary key, word varchar(40)); word_tag(word_id int not null, class_id int not null, primary key unique (word_id, class_id).

For each word, you enter into dictionary the word and its tags; then you enter into word_tag the word's id and its tags' ids.
posted by orthogonality at 9:04 AM on November 7, 2008

Sigh. Yes, orthogonality, I know how to do this with a database. It seems like such a common need, though, that I was hoping there'd be some way to do it with Excel.

So, turning to databases: do you have any recommendations for an inexpensive relational database for the Mac? As far as I can tell, you can't do this Bento, because it doesn't support multiple tables. Maybe I'm wrong about that. I'll look a little more. I could do it with Filemaker Pro, but I don't particularly want to spend $300.
posted by alms at 10:36 AM on November 7, 2008

As long as you don't want hundreds of pivot tables, I think you can do this in Excel.

Put your Words in Column A, your Lengths in B and your tags in C.
Your column headers should be in row 2 and your data should start in row 3, so row 1 is blank right now.

In Cell D1, you are going to put the single tag you are looking for. Cell D2 has the word "Match" in it and Cell D3 has this formula: =IF(FIND(D$1,C3)>0,"Y","N")

You will get a Y if the tag in D1 matches any tag and a #VALUE! if it doesn't. Make a Pivot table on the data in rows 2 and below. Put Match and then Length in the ROW area and then Word in the DATA area. Once the Pivot table is made, filter the Match column for only Y.

You can make a different pivot table for each tag, and as long as you don't refresh the data when you change tags, you're good. Alternatively, you could make many different columns just like column D and have different FIND formulas in each one.

This works on my Excel 2003 for Windows. I don't know if the older versions let you filter a pivot table. Even if they don't, you still get a really ugly version of what you want.
posted by soelo at 11:01 AM on November 7, 2008

This is the right way:

Drum 4 toy
Drum 4 instrument
Hawk 4 animal
Hawk 4 bird
Cat 3 animal
Cat 3 mammal
Cat 3 pet

That is, each tag needs its own unique row. This is bad in that you'll be doing some duplicate data entry, but your pivot table will pivot properly.
posted by VTCarl at 3:48 PM on November 7, 2008

alms writes "So, turning to databases: do you have any recommendations for an inexpensive relational database for the Mac?"

MySQL is free.
posted by orthogonality at 6:21 AM on November 8, 2008

Soelo comes closest to what I was looking for, but I guess the real answer is that Excel wasn't built for this at all.
posted by alms at 7:32 AM on December 28, 2008