Automating an alphabetical list from a spreadsheet

[DaveCinzano]

I maintain a spreadsheet to keep track of what films I have backed up on CD or DVD.

Each row represents a disc, numbered upwards from 1, with the name of each film in its own cell.

I would like to figure out a way of creating an alphabetical list of what films I have, preferably in a way which is automatically updated each time I update the spreadsheet (so when I burn disc #178, and type in the names of the films along row 178, I don't need to redo the whole alphabetical list).

I would also like the list to automatically generate the number of the disc(s) on which the film is against its name.

At the moment the spreadsheet looks like this:

Some issues which may complicate things:

Column A contains a manually entered number - the number of films on a given disc.

I differentiate television programmes and video-only or non-commerical releases from feature films (the focus of the spreadsheet) with brackets; I would like to make sure that these remain segregated from the main feature film list, but alphabetically ordered within their own sub-lists.

With non-English language films I tend to log them under both their native language title and the most commonly-recognised English title - eg "Talvisota / The Winter War". If possible I would like such entries to be double-logged, under both names with the alternative name following it. In other words I would like the list to be able to render automatically both "Talvisota / The Winter War" and "The Winter War / Talvisota" from the single spreadsheet entry B11.

Can anyone suggest how I might do this? Currently the spreadsheet is in AppleWorks. I also have Microsoft Office. Please bear in mind that I am looking for a not-too-geeky solution.

Any help would be gratefully received.

 

[free spirit]

don't know about appleworks, but in excel you'd just select the entire thing and use the sort function to sort it by whichever columns you wanted it sorted by - it can do multiple columns.

 

[the button]

Hmmmm.

This is in Excel: -

Probably something along the lines of creating a drop-down list of all your films using the "validation" function on the Data menu. Then create a template giving all the information you want about your films, and use LOOKUP (or VLOOKUP) formulas to automatically populate your template with the information once you've selected the film in the drop-down.

Doing it like this means that you can leave your source data as it is. It would be a lot easier if your source data was organised with a single line of data about each film (then you could just pivot it and do the automatic update thing), but it doesn't look as though it is.

It's hard to say without having the spreadsheet to play about with. If, that is, you take a "trial & error" approach to Excel.

 

[Shippou-Sensei]

just learn to database man

better in the long run





not entirely serious post i know people find it easier to use the tools they are already familiar with but seriously for manipulating data use a database

 

[the button]

I don't think Dave's data could meaningfully be exported into a database as it stands though (because it doesn't look like there's a single line of data for each film). Using VLOOKUPs isn't ideal (they're quite complex formulas, you can't strip in another column in your source data without fucking them all up, etc), but at least it could mean not having to re-input everything. This said, maybe you could contruct a single line by pivoting the source data. Hmmm.

 

[Mr Smin]

just learn to database man

better in the long run





not entirely serious post i know people find it easier to use the tools they are already familiar with but seriously for manipulating data use a database

It really is easier than you might think to have a database to do this.

 

[Shippou-Sensei]

I don't think Dave's data could meaningfully be exported into a database as it stands though (because it doesn't look like there's a single line of data for each film).

not as it stands but with a little though it would be a lot better

film table

auto id
name
alt name (null)
release (film, straight to video, non comercial)
backup id (forign key)
etc

backup table
backup id
format (cd, dvd)

or summin like that

now you could enter the info when you get the film and update the database when it gets burnt

 

[free spirit]

alternative being to just label each dvd...

 

[Thora]

alternative being to just label each dvd...

If you just rely on labels on the DVD, how would you be able to identify which disc a particular film is on without looking through each one?

 

[cybertect]

or summin like that

summin like that

 

[Shippou-Sensei]

yar

not sure if i'd do so much on the actor stuff ... perhaps just add an imdb link in the film table no point reinventing the wheel


i wonder how you would handle genre

text feild with csv ? actully no a well considered set is better

 

[DaveCinzano]

Thanks everyone for your replies.

However, I suspect that perhaps I wasn't clear about what I was looking for.

I maintain a spreadsheet to keep track of what films I have backed up on CD or DVD.

Each row represents a disc, numbered upwards from 1, with the name of each film in its own cell.

I would like to figure out a way of creating an alphabetical list of what films I have, preferably in a way which is automatically updated each time I update the spreadsheet (so when I burn disc #178, and type in the names of the films along row 178, I don't need to redo the whole alphabetical list).

I would also like the list to automatically generate the number of the disc(s) on which the film is against its name.

At the moment the spreadsheet looks like this:

Some issues which may complicate things:

Column A contains a manually entered number - the number of films on a given disc.

I differentiate television programmes and video-only or non-commerical releases from feature films (the focus of the spreadsheet) with brackets; I would like to make sure that these remain segregated from the main feature film list, but alphabetically ordered within their own sub-lists.

With non-English language films I tend to log them under both their native language title and the most commonly-recognised English title - eg "Talvisota / The Winter War". If possible I would like such entries to be double-logged, under both names with the alternative name following it. In other words I would like the list to be able to render automatically both "Talvisota / The Winter War" and "The Winter War / Talvisota" from the single spreadsheet entry B11.

Can anyone suggest how I might do this? Currently the spreadsheet is in AppleWorks. I also have Microsoft Office. Please bear in mind that I am looking for a not-too-geeky solution.

Any help would be gratefully received.

Whilst I am obviously very excited by all this talk of databases and geeky gobbledygook, it does not really address the issue I outlined.

I have a stack of DVDs and CDs with films on them. Each disc is numbered. I have a spreadsheet identifying what films are on each disc. I want to see if there is a way of creating a document which holds an alphabetical list of the films on the list, which automatically updates as the list itself is updated.

I only want to update the list when I burn a new disc. I do not want to have to write out DB records for each individual film. My query is centred around a desire to minimise the work I need to put in to manage two lists. The suggestion that I create database entries for each film, and entries that go beyond the film's title, clearly would involve far more work than even a manually updated alphabetical list.

Why do I want these lists?

Firstly, so I am able to quickly identify on which disc a certain film is. (The discs are stored in numerical order on 50 disc spools.)

Secondly, so it is easier to see what films there are when choosing what to watch (very useful when offering a guest a choice of films).

Again, thank you for you help and suggestions.

But please do bear in mind that I have a specific situation in mind, and also that I am not a geek.

 

[WouldBe]

Your best bet is the sort option (as in post #2) but that won't handle alternative film names as you've got them in your spreadsheet. You could add an extra column for the alternative name though.

 

[Shippou-Sensei]

Thanks everyone for your replies.

However, I suspect that perhaps I wasn't clear about what I was looking for.

Whilst I am obviously very excited by all this talk of databases and geeky gobbledygook, it does not really address the issue I outlined.

I have a stack of DVDs and CDs with films on them. Each disc is numbered. I have a spreadsheet identifying what films are on each disc. I want to see if there is a way of creating a document which holds an alphabetical list of the films on the list, which automatically updates as the list itself is updated.

I only want to update the list when I burn a new disc. I do not want to have to write out DB records for each individual film. My query is centred around a desire to minimise the work I need to put in to manage two lists. The suggestion that I create database entries for each film, and entries that go beyond the film's title, clearly would involve far more work than even a manually updated alphabetical list.

you were fairly clear about what you wanted it's just in this case what is obviously the best technical solution is a database however i freely admit this may not be an option for you given technical limitations

the extra information would only be used for extra benifits like for example if you wanted to find a good horror film you could easly find all your horror titles you don't have to include it

indeed you could manage this with a single table listing auto id. film name, alternate name, and cd/dvd no

this would be fairly easy to make with free tools and once the data was in you could play with it to your hearts content

Why do I want these lists?

Firstly, so I am able to quickly identify on which disc a certain film is. (The discs are stored in numerical order on 50 disc spools.)

Secondly, so it is easier to see what films there are when choosing what to watch (very useful when offering a guest a choice of films).

Again, thank you for you help and suggestions.

But please do bear in mind that I have a specific situation in mind, and also that I am not a geek.

all this stuff your talking about is totally database stuff having a database would make the task as simple as putting in a film name and it auto matically pumping out a cd/dvd number or even putting in a cd/dvd number and getting a list of films

yes i know you have something done in a spreadsheet and you don't want to spend time to learn a diffrent system but a database sytem is the right one for the job in the same way that you can eat soup with a fork but you should listen when some one talks to you about spoons


that beeing said this is your system so feel free to ignore this database stuff if it simply isn't something you feel comfertable with

 

[mhendo]

However, I suspect that perhaps I wasn't clear about what I was looking for.

Thing is, even though the idea of re-entering the information for each film might seem a bit of a hassle, the fact is that once you have the information in a simple database, it will be FAR easier to retrieve the sort of information you want.

The great thing about databases is that they excel (ha ha) at precisely the sort of data retrieval you're interested in here, and searching by Disc #, film title, film type, etc. is a piece of cake, and far more powerful (and easy) than a flat-file spreadsheet like Excel.

Also, in a database, any query (like, for example, "Show all my movies in alphabetical order") is automatically updated when you add new entries to the tables.

If you have MS Access on your computer, i'd be happy to set up a database with a form where you could easily enter your data, and with a few queries that would give you the results you want. If you don't have Access, i could do something similar in OpenOffice Base, a free alternative.

 

[Herbert Read]

Get a life and a girl / boyfriend instead.

 

[Shippou-Sensei]

or do what i do and buy TBs of external storage

no more burning to dvd ever

 

[cybertect]

If you have MS Access on your computer, i'd be happy to set up a database with a form where you could easily enter your data, and with a few queries that would give you the results you want. If you don't have Access, i could do something similar in OpenOffice Base, a free alternative.

Dave's OP Excel screenshot looks remarkably like a Mac, which would rule out Access.

The attachment I uploaded was from a very basic FileMaker database that took me about three minutes to knock up. Flattening the data from the Excel sheet to make it easy to import into a simple relational DB shouldn't be too difficult and eliminate the need to re-enter data.

If you want to do it with the tools you already have, then I'd suggest re-jigging your sheet in Excel.

One row per film instead of one row per disc.

| Disc No | Film Title | Alternative Title | etc.

Have a Max(Disc No) calculation cell somewhere on the sheet that tells you what the highest disc number you're currently using is, so you can then work out what the next number you will need for a new disc.

You can then use Excel's built in Sort or Autofilter functions to arrange the data to get the answers you're after.

Actually, this is more or less what Shippy suggested here.

indeed you could manage this with a single table listing auto id. film name, alternate name, and cd/dvd no

 

[cybertect]

Each row represents a disc, numbered upwards from 1, with the name of each film in its own cell.

<snip>

At the moment the spreadsheet looks like this:

I notice you seem to have some duplicate disc numbers in your existing data.

e2a: Ignore, I just spotted this

Column A contains a manually entered number - the number of films on a given disc.

 

[Shippou-Sensei]

Actually, this is more or less what Shippy suggested here.

yep you can easly do a one table solution

multiple tables would just help you to upgrade the system and do nice things

for example disks containing music as well as films

 

[Shippou-Sensei]

is it easy to lock data across rows in a spreadsheet?

 

[cybertect]

You mean for sorting to keep rows together? Excel does it by default with Autofilter sorting.

You have to go slightly out of your way to select the contents of a column to sort that independently of the other columns.

I don't think there's any way to permanently enforce locking data rows together, though.

 

[Shippou-Sensei]

recently i had to export some data to excel and when i selected a column (single click) did a sort on it it just sorted the column i wasn't sure what i did wrong for a while



mid you i was working with at excel foir mac on one computer open office on mine and windows excel on the bosses macine so i may have wich softwar work oddly mixed up

 

[DaveCinzano]

Hmm, some interesting food for thought since my last post, thanks all

To clarify, I'm not against the idea of switching from a spreadsheet to a database, I'm simply curious about my options for some way of maintaining the two lists (or a single list with two sets of usable information - the sequential disc list and the alphabetical content list) in the least labour-intensive way. I would not be averse to a brief but initially time-consuming switch from spreadsheet to database, if I felt that it would put me in a situation where once up and running it was very low-maintenance. In essence, the only time I want to spend typing anything is in the 30 seconds between dropping the avi files into my Toast window and actually burning the disc.

An issue which I haven't mentioned is that my preference is for the two lists to be periodically printed out (meaning there is a hard copy to give to someone which they can peruse and choose from). I don't think this really makes a difference re DB versus SS, but it might help illustrate why I am interested in this in the first place - I have a definite purpose in mind, and I want to develop repertoires which follow the function, rather than the other way round.

Again, thank you for your time and suggestions, it is appreciated

 

[FridgeMagnet]

Appleworks does contain a database if I recall; there might be some easy export option from spreadsheet to database.

 

[cybertect]

Half an hour with FileMaker on the train this morning (it allowed me to try some ideas I've been having with a couple of new features in FMP 10).

If you're interested, I can turn it into a run-time version that won't require you to have FileMaker (I have the developer edition of FMP) but it would need OS X 10.4.11 or later.

e2a: All the data entry is done from the Discs screen, so no extra work over your AppleWorks spreadsheet (apart from the optional adding of the position number to indicate the order you want the films to appear on the disc listing).

Printing and export to an Excel spreadsheet wouldn't be difficult to add.

 

[kabbes]

Why the hell do you have a copy of Basic Instinct 2?

 

[DaveCinzano]

cybertect: Heh - that looks grand, thank you for the kind offer If you are serious, I would be most grateful. I used to use FileMaker Pro, though I no longer have it on my box. I'm running Tiger 10.4.11.

kabbes: Because I am utterly non-discriminating in my viewing, and masochistically retentive in my backing up (Plus I wanted to see just how it matched up to the original. The opening sequence is rather well staged, if dreadfully written and performed!)

 

[cybertect]

I did a bit of tinkering at lunch time. Have a play.

Download 35 MB zip

If you think it will do the job well enough and want to send me over your appleworks sheet, I may be able to import your existing data to save you re-entering it.

 

[Shippou-Sensei]

i note two copies of alien with diffrent data