Automating alphabetizing a list

General Questions
#1

I have an MS word table that needs to be alphabetized by last name. Unfortunately, the name column is simply “participant.” Because there are many foreign and multiple-names, I can’t simply use text-to-columns or other simple trick to break up the names, alphabetize, and re-combine.

I know there are two or three duplicate last names, but I don’t know whether there are any last names duplicated in full names. As in “Gabriel Garcia Marquez” and “Jerry Garcia.”

The key to all this is a different spreadsheet that does list the participants by last name.

I haven’t opened Access for seven or eight years (and have never used Access 2007), so my trivially small skills are coated with rust. I’ve imported both as tables, and am trying to create a query that … that … this is about as far as I’ve gotten.

I figure I should be able to create a table based on relating table2’s ‘last name’ field to table1’s ‘participant’ field, then order table1 by ‘last name’, but I can’t remember where to begin.

I’m in one of those situations where I feel like there’s an easy, automated way to do this but I just can’t wrap my head around it and will likely spend more time than if I just did it manually—something XKCD has hit on a couple times. Does a solution jump out at anyone?

#2

Is the last word in the names always/reliably their last name? If so, you can use an Excel formula to sort a column by the last word in the row… let me know if that might work.

#3

This isn’t exactly what you’re looking for; however, it does the task I think you’re asking for. I use The Alphabetizer for sorting lists either alphabetically or randomly. I’ve been using it for a few years now for lists in English, Korean, and Chinese. If you have an entry duplicated, you’ll notice after the site alphabetizes it as the duplicate entry will appear immediately below its first instance.

I just entered a bunch of names in those three languages but not in alphabetic, 가나다, or character dictionary order. When I clicked the Alphabetize button, the site returned the list in correct dictionary order but with all the English names first, followed by the Korean names, then the Chinese names.

I guess you could cut and paste the words from your table to this site, alphabetize it, then cut and paste that list from the site to your table.

#4

Cool! Just entered the same random list into my Kingsoft spreadsheet and had the spreadsheet sort the data alphabetically. Got the same result as the site I posted just above.

#5

I would add an extra column to the original table, paste in the column of last names alone, alphabetize the original table by that column, and then remove it.

#6

I think that’s the root of my problem.

I have two tables, From_Document and From_Web.

The From_Document table has a simple ‘Participant’ field that has first, middle, and last names in one column.

The last word in the ‘Participant’ field is not necessarily the word to sort by.

I need to sort by the ‘Last name’ field of the From_Web table.

To make things worse, the From_Web table has 165 rows; the From_Document table has 157.

[quote=“Monty, post:3, topic:680696”]

This isn’t exactly what you’re looking for; however, it does the task I think you’re asking for. I use The Alphabetizer for sorting lists either alphabetically or randomly. I’ve been using it for a few years now for lists in English, Korean, and Chinese. If you have an entry duplicated, you’ll notice after the site alphabetizes it as the duplicate entry will appear immediately below its first instance./QUOTE]Great resource, but trying a few different times doesn’t quite match up with the From_Web’s order of things.

#7

Hmm… maybe I’m missing something. The two tables are in different orders. Maybe I’m missing the obvious… I’m going to alphabetize both by first name, then paste, then check for first name mismatches, then sort by last name.

#8

Don’t mean to bump for attention but to forestall any more thought. I started with Chronos’ idea, added a few check fields and a few iterations later ended up with my list.

#9

In a country like ours, with so many nationalities, we tend to refer to ‘family’ name rather than ‘last’ name. This avoids the problem of people like Saddam Hussein, where the patronymic is Saddam.

#10

Ah, didn’t realize that. I’m glad my idea could still be helpful, though.