Excel: Is it possible to sort by an arbitrary list?
July 7, 2013 3:29 PM Subscribe
I have a list of 625 English words, translations in a bunch of languages, and what-not in a giant excel file. We'll call that excel file "the Data." The Data is in alphabetical order. I also have a separate list of those same English words in a different order. Is there a way to sort the Data so that it's in the same order as my new, non-alphabetical list?
The Data: As mentioned above, I have 625 English words and their translations. Entries look like this (dashes to signify new columns): WORD – translation – pronunciation notes – other notes. So I might have: school – une école – é is pronounced like 'ay' in 'day' – une école maternelle (preschool/kindergarden)/une école primaire (elementary school)/un collège (middle school)/un lycée (high school). I have these lists of data in a bunch of languages, each with 3-5 columns of supplementary information.
The Order: I've spent the past few weeks grouping the English words into thematic groups: "cat, to chase, 3, blind, mouse" "102, temperature, hot, summer, sweat, August, pool." I want to group my data in those thematic groups, without having to copy and paste 625 times. Is there a way to do this automatically?
The Data: As mentioned above, I have 625 English words and their translations. Entries look like this (dashes to signify new columns): WORD – translation – pronunciation notes – other notes. So I might have: school – une école – é is pronounced like 'ay' in 'day' – une école maternelle (preschool/kindergarden)/une école primaire (elementary school)/un collège (middle school)/un lycée (high school). I have these lists of data in a bunch of languages, each with 3-5 columns of supplementary information.
The Order: I've spent the past few weeks grouping the English words into thematic groups: "cat, to chase, 3, blind, mouse" "102, temperature, hot, summer, sweat, August, pool." I want to group my data in those thematic groups, without having to copy and paste 625 times. Is there a way to do this automatically?
Best answer: Assuming your non-alphabetical list is on another tab or file, use VLOOKUP to pull the other columns from your alphabetical list into that tab. Let's say the columns you have data in are A-E on Sheet1 and your nonalpha list is in column A on Sheet2. You would put the following formula in Sheet2 cell B1: =VLOOKUP($A1,'Sheet1'!$A:$E,2,FALSE) and then copy it over to C1, D1 and E1. Then you change the 2 (before FALSE) to 3 in column C, 4 in column D and 5 in column E. Then autofill B1-E1 down the entire sheet.
Vlookup will find a value you designate (A1 in this case) in the range you reference ('Sheet1'!$A:$E) and then go to the column you say (2) to find the data in that cell.
posted by soelo at 3:57 PM on July 7, 2013 [3 favorites]
Vlookup will find a value you designate (A1 in this case) in the range you reference ('Sheet1'!$A:$E) and then go to the column you say (2) to find the data in that cell.
posted by soelo at 3:57 PM on July 7, 2013 [3 favorites]
Response by poster: Is there a way to customize how vlookup handles multiple entries in the data? I have a few columns where there's a header (Say "ANIMAL") and then various entries (Dog, cat, etc.), one of which is often *also* the header name (Animal).
posted by sdis at 4:03 PM on July 7, 2013
posted by sdis at 4:03 PM on July 7, 2013
soelo's approach is more robust, but this is a quick hack which should work.
1. Add a column to the list which is in the order you want. Call it 'Sort Order' or something.
2. Fill Sort Order with numbers from 1 to 625. This is the order you want the list to appear in.
3. Sort the data alphabetically. The numbers in Sort Order will get scrambled up. Both lists should now be in the same order -- alphabetical.
4. Make a new column in the sheet with alphabetical data.
5. Copy the scrambled numbers from Sort Order, and paste them into the new column on the alphabetical sheet.
6. Sort both sheets by Sort Order. They should match.
posted by PercussivePaul at 4:05 PM on July 7, 2013 [3 favorites]
1. Add a column to the list which is in the order you want. Call it 'Sort Order' or something.
2. Fill Sort Order with numbers from 1 to 625. This is the order you want the list to appear in.
3. Sort the data alphabetically. The numbers in Sort Order will get scrambled up. Both lists should now be in the same order -- alphabetical.
4. Make a new column in the sheet with alphabetical data.
5. Copy the scrambled numbers from Sort Order, and paste them into the new column on the alphabetical sheet.
6. Sort both sheets by Sort Order. They should match.
posted by PercussivePaul at 4:05 PM on July 7, 2013 [3 favorites]
vlookup generally sucks for data that isn't formatted perfectly. I'd use MATCH which will return a row number in your array and then OFFSET or INDEX to get the data in the next column.
posted by JPD at 4:44 PM on July 7, 2013
posted by JPD at 4:44 PM on July 7, 2013
Can't you just combine the two, sort the columns alphabetically and do a dupe delete?
I've gotta tell you, the only thing I miss about corporate is using Excel. I love nothing more than massaging a huge spreadsheet into something I can endlessly play with. I used to run reports just to play new tricks when I was bored.
posted by nevercalm at 5:22 PM on July 7, 2013 [2 favorites]
I've gotta tell you, the only thing I miss about corporate is using Excel. I love nothing more than massaging a huge spreadsheet into something I can endlessly play with. I used to run reports just to play new tricks when I was bored.
posted by nevercalm at 5:22 PM on July 7, 2013 [2 favorites]
This thread is closed to new comments.
Going further into Google, it appears that it may be possible to do this by "creating a lookup table, then adding a column to the data with a MATCH formula, and then sorting by that column." Unfortunately I have no idea what on earth that means. Attempting to fix that, but if anyone can chime in, I'd appreciate it!
posted by sdis at 3:42 PM on July 7, 2013