Sorting Japanese names in a consistent way

timtak

New Member
Joined
Oct 28, 2009
Messages
3
I want to sort lists of Japanese Surname Kanji in a consistent way.

I get lists of students grade related information from three difference sources.
1) The course role call file supplied by the university study support system
2) The online homework system I am using
3) Our Departmental exam marksheet reader

I want to merge this grade information together but when I sort on surnames, using values, A to Z the sort is performed differently depending on the source of the surnames (1-3). When I am lucky I can sort on a student number, but not always.

The differences between the sorts does not appear to depend upon phonetics in the cells since names with the same phonetics are sorted in different ways depending upon the source (1-3 above).

I have no idea what is causing the different sort order. I have Excel 2003 and 2013 and neither has a "sort on phonetics" option, to turn off or on in the sort options dialogue.

In the end I end up matching the names by dragging and dropping cells by hand which is fraught when there are classes of 70 plus students some with identical surnames.

Here is an example showing three Japanese surnames in columns A and C which are sorted in different ways.
http://nihonbunka.com/temp/Japanese_name_sort_problem.xlsx

The "I want to get my data to look like the below" was achieved by dragging and dropping NOT by sorting.

Should anyone have any suggestions as to what information may be contained in the cells that is effecting the sort order, please let me know.






I repeat that this does not seem to be a phonetic reading problem since the phonetic readings appear to be the same, but for the record, I have tried removing phonetic characters using the following three methods
1) Copy> past special > Formulas
2) Sub DeletePhonetics()
Dim rng As Range

For Each rng In Selection
rng.Characters.PhoneticCharacters = ""
Next rng
End Sub

3) Sub DeletePhonetics()
Dim rng As Range

For Each rng In Selection
rng.Phonetics.Delete
Next rng
End Sub

When I use the vba above and display the phonetics field it appears to be empty but then when I click on edit phonetics, a phonetic reading is displayed which does not seem to be vanilla, but intelligent (how students actually say their names) so it either as if the above deletion scripts are not working or that clicking on Edit Phonetics restores
the previous reading.
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

timtak

New Member
Joined
Oct 28, 2009
Messages
3
No! It does seem to be phonetic character related. That and the fact that some name characters are special characters. The strange thing is that "display phonetic characters" and "edit phonetic characters" displays different content. The VBA scripts above (or at least 2) does seem to delete the phonetic displayed when "display phonetic characters" is selected, but if one then chooses "edit phonetic characters" the phonetic characters that one has just deleted are displayed again, as a default suggestion perhaps.

I am getting there slowly, I think.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,176
Messages
5,600,154
Members
414,367
Latest member
dw970906

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top