Sorting a spreadsheet

[John873]

I have a spreadsheet which contains 3 columns of data about movies at a film festival.

The first column contains the names of the theaters at whcih a film is shown. Ther are 4 theaters

The 2nd column contains the time the film is show

and the 3rd column contains the title of the film

Is there any way to sort this data so that the 4 theaters appear at the head of 4 separate columns and the rest of the data can still be sorted by time and the title of the films will appear below the name of the theater at which they are shown?
EXAMPLE
Regal Crown Uptown Hill
9:00AM title
9:30 AM title
10:00AM title
etc, etc

I know how to do the standard sorting when all of the data appears in 3 adjacent columns but I wonder if the sort I am proposing is possible?

Thanks,

John

[Barry]

I have a spreadsheet which contains 3 columns of data about movies at a film festival.

The first column contains the names of the theaters at which a film is shown. There are 4 theaters

The 2nd column contains the time the film is show

and the 3rd column contains the title of the film

Is there any way to sort this data so that the 4 theaters appear at the head of 4 separate columns and the rest of the data can still be sorted by time and the title of the films will appear below the name of the theater at which they are shown?
EXAMPLE
Regal Crown Uptown Hill
9:00AM title
9:30 AM title
10:00AM title
etc, etc

I know how to do the standard sorting when all of the data appears in 3 adjacent columns but I wonder if the sort I am proposing is possible?

Hi John,

Try this:

Row 1:

A: Theatre, B: Time, C: Title, D: Regal, E: Crown, F: Uptown, G: Hill

Row 2:

A,B, C contain the data as in your initial example.

D, E, F and G contain this formula.

=IF(LEFT($A2,1)=LEFT(D$1,1),$C2,"")

Enter the formula in D2, select D2..Gn (where n is at least one more than the number of titles you will list and press command-R to Fill Right, then command-D to Fill down.

The formula will automatically adjust to fit the cell it is in.

Enter the data in any order (in the first three columns only), then select everything except the column headings and do an ascending sort on B2 (with A2 as the second index if you want the theatres to be listed in the same order where their times are the same).

To print (or view) a table like the one in your example you can either collapse (ie set column width to 0) columns A and C, or Insert a column to the left of the current column D to hold a copy of the list of times, then print only columns D..H. Copy the contents of column B to (then new) column D with this formula, placed in D2 then filled down:

=B2

Note that with the first formula you can enter as little as the first letter of the theatre name in column A, provided all the theatre can be distinguished from each other using only their initial letter.

Regards,
Barry