excel: multiple entries in a cell? (PIC)

[the block]

I wasn't quite sure how to phrase the title, but what I need to do is create a spreadsheet that contains the titles, authors, and where the book/article pdf is backed up to. The problem is some books/articles have multiple authors. The person that asked for my help on this, also wants to be able to sort the list by author, and doesn't want to create multiple rows for books/articles with multiple authors.

For example, in the picture below, say you want to sort by author and find a book by George Bush. You wouldn't be able to find it easily because Barack Obama's name is listed first and it would be listed under B, instead of G.

Does anyone know a good solution to what I'm asking here? Any help is appreciated

thanks

 

[drdeutsch]

The only thing I could think of would be to take 3-4 (or however many cells you need to cover the space) in row 1 and merge them. Leave the cells in rows 2 and below unmerged. Then you could put 1 author in each cell, 2b, 2c, 2d, 2e, etc, but you'd have the "Author(s) merged and centered above them.

 

[pxc]

You can write VBA macros to do that, but IMO it's a really bad idea to try to use a spreadsheet like that. It would be better organized as a multi-table database, at least to split out the authors and make sorting easier. Access can handle that if you want to make the results available in a table (using a grid control) similar to Excel.

There are probably free collection management software packages that would be easier to use and maintain.

 

[k1pp3r]

Why don't you just use a wildcard search,

Ctrl + F
Search string *George*

 

[Usquabach]

Kind of a lame solution but you could use text to columns

i believe it's under "tools" --> text-to-columns

then choose delimited and choose to separate values by comma

then each author will be in their own column which you can sort much easier. just sort the first column, then the second and third and so on but keep the values aligned (should be a sort option called "then by")

if that doesn't work use a formula like

=if(countif(range,"criteria")>0,value if true,value if false) but i don't really have access to excel on this comp and this is sort of a guess

GL!

 

[SC385]

I think you'd be better off using a database instead of a spreadsheet. Especially if there are a lot of books.

 

[tel1jag]

I gotta say that your requirements are much more database oriented then spreadsheet oriented. I would use MySQL or whatever (or even access if it's going to be small) and that will give your searching real power (a la relational db).

I bet you can find something on sourceforge or freeware that is already built for this.

I know I am not helping with your spreadsheet/Excel question, but just wanted to make sure you considered this...

--Jeff