Please help me sort my vocabulary list in Excel!

May 27, 2008 1:34 PM

Complicated Excel formula question involving text-search and Japanese characters. Can you help?

I have two spreadsheets.

Spreadsheet A contains a list of Japanese vocabulary words I need to learn for the JLPT this year. Spreadsheet B contains a list of Japanese characters in one column along with the characters "Heisig number" (an ordinal number indicating where that character appears in the book "Remembering The Kanji" by James Heisig).

I want to add a column to Spreadsheet A that will contain the highest Heisig number among the characters that make up the vocabulary word. The purpose of this is to sort the words in Spreadsheet A so that I can memorize only the words that can be written using the Kanji I've learned so far.

Confused? Here's an example: Spreadsheet A, cell 57A contains the word "朝寝坊". Using Spreadsheet B to looking up the 3 characters in that word, I find the Heisig number for 朝 is 52, 寝 is 1150, and 坊 is 492. 1150 is the highest of those 3 numbers, so I'd like Spreadsheet A, cell 57B to read "1150". Is there a way to set up a formula to do this? I've never pushed Excel this far before.

Thank you in advance, Hive Mind!

posted by Vorteks to computers & internet (2 comments total)

Hardly the most elegant solution on my part but
1. Put heisig.xls in a second sheet ov vocab.xls and name the range "reference"
2. Use =MID($A2,1,1) to =MID($A2,15,1) to split out the japanese cell into cells B to P containing each character (the longest string is 15 characters so you only need 15 columns)
3. Use VLOOKUP to lookup the Heisg number for each Japanese character try [=IF(ISNA(VLOOKUP(B2,reference,2,FALSE)),0,VLOOKUP(B2,reference,2,FALSE)] to remove N/A results from the lookup so you only have real numbers returned from the "reference" range.
4. Use Max() for the results to return the higest number.
5 Fill the results down.

I am sure someone knows an array formulae that can accomplish the same thing but this is the quick and dirty way. I just PM'd you if you want the sheet that i put together.
posted by clarkie666 at 3:01 PM on May 27

That is about how I would do it too. You can make it fancier if you want but by the time that hide all of the appropriate cells, this gives you exactly what you are looking for with no extra mess in the way.
posted by milqman at 4:43 PM on May 27