Excel help sought

[Hofmann]

In the midst of making 平水韻 flashcards for Anki, I'm stuck.

I've got an Excel file at this point, where the rhyme is in column A and the character is in column B. Of course, there are some homographs, so some characters appear more than once in column B, but with a different rhyme. For example, the character 參 has the rhymes 下平聲十二侵, 下平聲十三覃, and 去聲二十八勘. I'd like to combine them such that a cell in column A has "下平聲十二侵, 下平聲十三覃, 去聲二十八勘" and the corresponding cell in column B has "參" but I'm lost on how to do that. I'm not doing that manually because there are thousands of homographs.

[creamyhorror]

It would be easier and more flexible to make a new table from your existing data:

structure: [ Character | Tone 1 | Tone 2 | etc. ]

(Characters are unique, not to be repeated; tone columns are to be filled with boolean YES/NO values)

For each cell in the tone columns, use a LOOKUP or similar function to check for the character-tone pair in the original dataset. If true, put a "YES" in the cell; otherwise leave it blank.

edit: Sorry, looks like the MATCH function is more appropriate

You can then format and use this table directly. If you want the format you initially requested (2 columns only), you can use CONCATENATE functions in the columns directly to the right of the table to add successive tone names together to give you what you want.

I only have OpenOffice on my computer but I can do it for you if you want.

[cababunga]

Here, I made you a tab separated file. I'm sure you can import it in Excel.

Hmm... Are attachments suppose to show up anywhere?

Ok, put it here:

http://www.mediafire.com/?2ettwuk2h679dga

Edited March 5, 2011 at 06:00 AM by cababunga

[creamyhorror]

Damn, OpenOffice's MATCH function can't do what Excel can. Fail.

Anyway I guess the problem's solved.

[Hofmann]

Damn! May I bother you to explain how you did that?

[cababunga]

Sure. First I saved your spreadsheet as a tab-separated values and then in interactive Python session did the following:

from collections import defaultdict
d=defaultdict(list)
for x in open('平水韻 flashcards.txt'):
   r, c = x.split('\t')
   d[c].append(r)

f=open('平水韻 flashcards.tab', 'w')
for c, r in sorted(d.iteritems()):
   print >>f, "%s\t%s" % (' '.join(r), c),

[imron]

I was going to make one in Vim using regexes, but it looks like cababunga beat me to it

[Hofmann]

It looks like there are no problems. It's a shared deck named 平水韻 Pingshuiyun. Thanks for the help.