Hi Steved!
There are some things that need to be considered before
going this route. The "database" I have created is very
specialized and for a very specific task. Depending on the
size of your collection, a real database application may
be better. My collection is not that big. I used Excel
because:
1. The collection size is relatively small. ~9000 entries
2. I don't have a database application.
3. I didn't want to go out and spend hundreds of $$ for a
database application that, at best, would get very little
use.
4. If I did go out and buy a database application, how
long would it take me to learn enough about it to actually
use it when I already know how to use Excel.
That being said:
The hardest part of putting this together was entering all
the data. Very time consumming and tedious. The formulas
were the easy part.
On a worksheet I entered the data in this layout:
Col A = artist name
Col B = album name (includes cds, tapes, etc)
Col C = song title
Then I sorted by col A then by Col B.
Now, here's a tip that may come in handy and eliminate
some confusion later. I discovered this after the fact.
Some of the formulas use the MATCH function. You may have
duplicate songs but on different albums or by different
artists. Because the MATCH function will always find the
first instance of the lookup value, this could lead to
problems and having the incorrect data returned. For
example, in my collection I have a song title: "I Don't
Live Today", that appears on 3 different albums and by 2
different artists. The solution to this is to add a unique
identifier to each instance of the song. This is very easy
to do. You can use a simple COUNTIF and find those dups
then add the unique identifier: EG:
I Don't Live Today (1)
I Don't Live Today (2)
I Don't Live Today (3)
Now, with the unique identifier, the lookup/match will
return the correct data.
Now, the formulas:
On a different sheet at the top, I use cell C1 to enter
the "search criteria". In cell C2 I have a formula that
counts the number of criteria matches that were found. In
Col A there is a formula to return the artist. In Col B
there is a formula to return the album name and in Col C
there is a formula to return the song title that matches
the search criteria.
Formulas:
Col C: =IF(C$1="","",INDEX(dbase!C$1:C$2287,SMALL(IF
(ISNUMBER(SEARCH(" "&C$1&" "," "&dbase!C$1:C$2287&" ")),ROW
(C$1:C$2287)),ROW(1:1)))) Entered as an array.
Col B: =IF(C5="","",INDEX(dbase!B$1:B$2287,MATCH(C5,dbase!
C$1:C$2287,0)))
Col A: =IF(C5="","",INDEX(dbase!A$1:A$2287,MATCH(C5,dbase!
C$1:C$2287,0)))
Of course, adjust for sheet names and range sizes!
Now, you need to decide how many records you want
returned. That is, if your search criteria is the
word "of" , there could be hundreds of matches. On the
other end of the scale, if your search criteria is the
phrase "I Don't Live Today" , there may be only 3 matches!
So, with that in mind, figure out how many records you
want returned then just copy all 3 formulas down that
number of rows. I use Conditional formatting in Col C to
hide the error value #NUM! when no matches are found.
The search criteria can be as little as a single word or
any combination of words but the search looks for the
string as a whole. EG: search for: in the
Will find:
A Day IN THE Life
Look IN THE Mirror
A Walk IN THE Park
Will not find:
IN Through THE Out Door
Come IN And Join THE Party
Since you've asked about this I'm now going to tinker
around and make this even more robust! Like I said, I use
this for a very specialized reason and it's design
reflects that. It could easily be expanded and improved
upon to add more "features"
It may have been easier to just send you a copy!!!!!
Biff
