how do I sort dictionaries by value then by key?

How do you sort a list of titles in Excel by the complete value (not just first word)?

  • In Excel I have a list of titles and would like to sort the data in alphabetical order. It seems that Excel will sort the values based only on the first word in the cell. The problem with this is that I have another spreadsheet that checks this list of titles using a 'VLOOKUP' function and that goes through the target list alphabetically using the whole cell. Thus when a cell is out of order in the target list by the second word (ex. A1 = "The Back" A2 = "The Apple"), the 'VLOOKUP' function when searching for "The Apple" will stop at the first cell. Hope this makes sense.

  • Answer:

    Excel will sort by the full string, so I am a bit mystified by the result you are getting.  For example Albert Adams and Albert Zimmerman sort as you would expect - it isn't the case that they sort only on 'Albert'. Equally, VLOOKUP will look at the whole cell, irrespective of the order - there is no requirement to sort a list in order to use VLOOKUP.  There is, however, an argument for the VLOOKUP function - TRUE or FALSE. If you select FALSE, the formula will look for an exact match, and will return no value if an identical string is not found.  If you choose TRUE (which is the default if you put nothing) the formula may return the closest match to the reference string.  It's easier to see with numbers: If you have a list 1,3,5,7,9, and you VLOOKUP the value 2 then FALSE will return #N/A, and TRUE will return 1 (it selects the last value from the list before the search value is exceeded).  You may find that using this argument fixes your lookup issue.

David Hardstaff at Quora Visit the source

Was this solution helpful to you?

Just Added Q & A:

Find solution

For every problem there is a solution! Proved by Solucija.

  • Got an issue and looking for advice?

  • Ask Solucija to search every corner of the Web for help.

  • Get workable solutions and helpful tips in a moment.

Just ask Solucija about an issue you face and immediately get a list of ready solutions, answers and tips from other Internet users. We always provide the most suitable and complete answer to your question at the top, along with a few good alternatives below.