Hi

I need a possible solution.

I am a mobile dj and have a database created in excel of my entire cd
collection it goes like this...

Artist - Song - Cd Title - Duration - Cat No.


In this database i have so far 10,000 cds hundreds of thousands of tracks
split over 5 sheets.

Now i have just finishing typing in the Uk Charts in a seperate excel
database which consists like this

Artist - Song - Year - Chart Position


Now i would love to be able to remove all of the tracks i have in my cd
collection from the uk chart database, so leaving only the uk chart tracks i
don`t have.


Is this possible if so how? `im newish to excel 07(came with pc).

thanks

Can you be sure that you have typed the artists' names the same in
both databases, and that the song titles are spelt identically in
both? As you will be looking for an exact match then these will have
to be the same.

You will want to match on both Artist and Song, so I would suggest
that in your CD database you use a new column (F) to join these
together like so:

=TRIM(A2)&TRIM(B2)

and copy down the column. You will also need to do this on your other
four sheets of this database.

Then in your chart database you can add a formula in column E along
the lines of:

=IF(ISNA(MATCH(TRIM(A2)&TRIM(B2),Sheet1!$F:$F,1,0) ),"no","yes")

This will check only against Sheet1 of your database (which for
simplicity I am assuming is in the same file as your Chart database),
and will return a yes or no depending on whether a match is found. You
could apply a filter to this column to select all the yes answers, and
then highlight the visible rows and Edit | Delete Row. Then select All
from the filter pull-down.

You have now removed all the tracks that are in the first sheet of
your collections database, so you can amend the formula so that it
looks at the second sheet, and then copy the formula down. Again,
filter for yes and delete the visible rows, and repeat this for all 5
sheets that make up your collection database.

Eventually you will have remaining all the chart songs that are not in
your collection.

Hope this helps.

Pete

On Nov 27, 10:45 pm, "Fred" wrote:
Hi

I need a possible solution.

I am a mobile dj and have a database created in excel of my entire cd
collection it goes like this...

Artist - Song - Cd Title - Duration - Cat No.

In this database i have so far 10,000 cds hundreds of thousands of tracks
split over 5 sheets.

Now i have just finishing typing in the Uk Charts in a seperate excel
database which consists like this

Artist - Song - Year - Chart Position

Now i would love to be able to remove all of the tracks i have in my cd
collection from the uk chart database, so leaving only the uk chart tracks i
don`t have.

Is this possible if so how? `im newish to excel 07(came with pc).

thanks

Layout examples with before/after look

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Fred" wrote in message
...
Hi

I need a possible solution.

I am a mobile dj and have a database created in excel of my entire cd
collection it goes like this...

Artist - Song - Cd Title - Duration - Cat No.


In this database i have so far 10,000 cds hundreds of thousands of tracks
split over 5 sheets.

Now i have just finishing typing in the Uk Charts in a seperate excel
database which consists like this

Artist - Song - Year - Chart Position


Now i would love to be able to remove all of the tracks i have in my cd
collection from the uk chart database, so leaving only the uk chart tracks
i don`t have.


Is this possible if so how? `im newish to excel 07(came with pc).

thanks