Spreadsheet tips with Lotta Rosenkallums . . .

Sorting Names Containing Prefix Articles

© 2003 Information Disciplines, Inc., Chicago

NOTE: This article may be circulated freely as long as the copyright notice is included.

The need

Names of organizations, names of literary works, and other names often begin with an English article: or a foreign language equivalent. How do we store such names in a file or a database if we need to sort them alphabetically? It would be unfriendly to expect users to look under T for the bank or under A for the Dickens novel. On the other hand, we don't want to chop off the articles that are an integral part of the name.

The usual solution in procedural programming is to store the name in two fields. We sort on the one containing the body of the name, e.g. "Tale of Two Cities", and we display the concatenation of the two.

That solution is also available and fairly easy in spreadsheet models.

Spreadsheet technique

The following solution works in both Quattro Pro® and Excel®.

Entering the data

We put the actual data in two hidden columns, say Y and Z.

YZ
TheBarber of Seville
 Carmen
  Don Giovanni
  Madama Butterfly
TheMarriage of Figaro
DieMeistersinger
DerRosenkavalier
  Salome
La Traviata
Il Trovatore
  Turnadot
A View from the Bridge

When we sort (Data Sort command) we use the right column as the key.

Hiding the columns

There are four ways of concealing a column from the normal spreadsheet display:
  1. Hide the column. First right click the top of the column to select it. Then
  2. Put the columns on a separate sheet.
  3. Set the column width to 0.
  4. Place the columns off the screen at the extreme right,

Your choice should depend on the complexity of the whole spreadsheet model, the frequency of entering new data in those columns, and the spreadsheet processor you're using.

Displaying the names

Suppose the two columns described above are Y and Z and the data begin in row 1. Then enter the following formula in the first row of the column where you want the names to be displayed.

=if(isblank(Y1),Z1,Y1 & " " & Z1) ---- (Excel)
@if(@isblank(Y1),Z1,Y1 & " " & Z1) -- (QuattroPro)

Alternative formulas, some simpler, are available in both spreadsheet processors, but this one (except for the QuattroPro function designator @) works for both. It will almost surely be handled correctly should anyone ever import this spreadsheet model into a different spreadsheet processor.

The result will look like this:

D
The Barber of Seville
Carmen
Don Giovanni
Madama Butterfly
The Marriage of Figaro
Die Meistersinger
Der Rosenkavalier
Salome
La Traviata
Il Trovatore
Turnadot
A View from the Bridge

That's the way most users would expect the list to be alphabetized.

Note: This column should be protected so that users don't forget and enter data into it.

Return to Spreadsheet guidelines
IDI home page

Last modified 15 May 2004