Excel - Problem with an Index

  Border View 19:28 05 Feb 2004

Hope you can help with this one.

I have opened a spreadsheet in Excel to index my family history book. I am trying to index all of the names.

Column A is for the Surname
Column B is for the Christian Name
Column C is for Year of Birth
Column D is for Page No.

By listing all entries on every page I thought all I would need to do would be to highlight all four columns and all 300 rows and they would sort themselves into alphabetical order - thus I would be able to extract names with page numbers. But what happens is that the Surnames all sort into alphabetical order but not with the christian names in order. They sort themselves into Surnames by year of birth.

There must be an easy way of doing this, I just dont know what it is. Can anyone please advise.

Your help would be much appreciated.

  Valvegrid 19:43 05 Feb 2004

Have you tried clicking on data then sort, a box will open that allows you to sort in column order.

As a matter of interest have you thought about setting an Access database up? This will let you set up all sorts of queries and you can print reports off that look real posh.


  gazmania 19:54 05 Feb 2004

If you haven't already done so, ensure the first row of each column contains a heading, e.g. Surname,Forename ,Birth Date, Page No. , in order to create a table (database). Place your mouse pointer anywhere in the table below the headings. Click Data, then Sort. You will be given a choice of columns to sort by your headings (ensure the "my first line contains the heading" is checked).
Hope this helps.

  VoG II 20:22 05 Feb 2004

With or without column headinds, if you select all the data then Data/Sort the Wizard will let you sort by several column values.

  VoG II 20:22 05 Feb 2004

With or without column headinds, if you select all the data then Data/Sort the Wizard will let you sort by several column values.

  Border View 23:09 05 Feb 2004

Many thanks for your response. Sorry I've just got back to you. Been watching the Hibs/Rangers Match.

Valvegrid, I'm not very conversant with Access, but have a tutorial CD.

VoG, will give the Data/Sort Wizard a go tomorrow. Shall let you know how I get on. Just knew if I gave my query the heading with Excel that you would respond. Thank you.

  VoG II 00:46 06 Feb 2004

The penny has just dropped.

Obviously, you need to keep the forenames and surnames together in this Sort.

Let me sleep on it.

  VoG II 07:42 06 Feb 2004

Create a new Column E. Assuming that your entries start in Row 1 then in E1 enter the formula

=A1 & " " & B1

Then hover the cursor over the bottom right of A1 until the cursor turns into a + then hold down the left mouse button and drag down to copy the formula to all your other rows.

Then do a sort, using Column E as the sort key. You can delete Column E after sorting if you wish.

  Megatyte 09:37 06 Feb 2004

I don't understand. Do you mean that the surname is moving without taking the christian name with it?

Incidentally, there's no need to select the whole range. Just select a cell within the range and Excel's sort will expand the selection to cover all occupied cells (unless you have any empty columns/rows in the range)


  JoJoh 13:16 06 Feb 2004

If I understand right, all you want to do is to put the columns in alphabetical order. An easy way to do this is to highlight all of the cells in your table making sure that the first column is the one that you want in alphabetical order.

Than press the A-Z button on your toolbar. This will have the desired effect. I never use the data function as I don't know how. I will now look at it. Hope this works

  Border View 15:33 06 Feb 2004

Many thanks for your responses. Before logging on to the internet I had a play about with Data/Sort.

Highlighted all four coloums - then with the data sort wizard Instructed Sort column A into alphabetical, then Column B and then column C. There is only space on the wizzard for three columns instructions. Clicked the button and they sorted themselves out taking their page number (column D) with them.

Before doing the above, I copied the whole lot onto another sheet so that I could play canny without loosing any data.

Will double check and let you know if it is correct.

Again many thanks for your responses. Thanks VoG for pointing me in the direction of Data/sort.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

AMD Radeon Adrenalin release date, new features, compatible graphics cards

Indie publisher Canongate’s top 10 book covers of 2017

New iMac Pro release date, UK price & specs rumours

Tablettes Amazon Fire : quel modèle choisir ?