Lists

[G3£wh\)zZ@]

We are two dj`s who have gotten together and we need help with our music
collection lists.

I have a list of the complete uk music chart consisting of

Artist, Title,Chart Position, Year

We also have 2 lists of our music collection of records,cd`s,md`s and mp3`s

Artist,Title,Cd Title,Format,Quality

We would like to add the chart position and year (from the 1st list) to our
music collection list, without manually going through it all copying and
pasting

Is this possible (However my friends list is not 100% spelt accurate and may
include duplicate songs on different media etc) Is it still possible?

--
Then we would like to create a list containing the songs from the chart list
that we don`t have is this possible?


Thanks for any input in solving this.

 

[Frank Kabel]

Hi
the following will get the chart position and the year if both article
and title are spelled in the same way. If you have titles/artists
spelled differently this would require some manual effort.

Put the following array formula (entered with CTRL+SHIFT+ENTER) in cell
F1 of your second list (assumption: col. A is the artist and col. B the
title on both lists):
=INDEX('list1'!$C$1:$C$1000,MATCH(1,('list1'!$A$1:$A$1000=A1)*('list1'!
$B$1:$B$1000=B1),0))

and in cell G1 put the array formula
=INDEX('list1'!$D$1:$D$1000,MATCH(1,('list1'!$A$1:$A$1000=A1)*('list1'!
$B$1:$B$1000=B1),0))

 

[Bob Phillips]

Matching is simple. Based upon track being in column B on separate sheets,
you could use VLOOKUP

=VLOOKUP(B2,Sheet2!B21000,2,FALSE)

foir position, ,3,FALSE for year.

Matching/duplicates is more difficult, and is probably best handled by
identifying them and correcting them. If you add a mformula in a blank
column on your primary list with a formula like
=COUNTIF(B2,Sheet2!B:B)
if it is 0 or greater tha n 1 these need investigating. Similarly on the
chart position sheet add a similar formula looking back into Sheet1.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[G3£wh\)zZ@]

Thanks for the replies, i will print your replies and try both suggestions
on a copy of the lists etc.