Set message subject here ...

[Cal]

I use excel to store my audio cd information, i have a big collection of music cds

I have it like

Column A = Currently blank
Column B = Artist
Column C = Track
Column D = Cd Name


Then say a cd as 10 tracks then it will be 10 Rows containing artist, track name, cd name etc.


What i would like if it is possible so far i do it manually but would like it easier etc.

Where i drag down column A and it will automatically number each cd for me, it would be similar to this...



0001 / Prince / Alphabet Street / Greatest Hits
0001 / Prince / 1999 / Greatest Hits
0002 / Madonna /Vogue/Greatest Hits 2
0002 / Madonna /True Blue/Greatest Hits 2



So the number is the same for each track on that cd but changes when it`s a different cd.

Is this possible?

 

[ck13]

Hi,

See if this works =IF(SUMPRODUCT(--(B2=B1),--(D2=D1))=1,A1,A1+1)

 

[ck13]

Hi,

Sorry to mentioned that in A1, you need to put in number 1 and the formula
goes into A2

And if you want to 0 in front of the number, try this
=TEXT(IF(SUMPRODUCT(--(B2=B1),--(D2=D1))=1,A1,A1+1),"000#")

 

[Mike H]

Hi,

Firstly apply a custom format of 0000 to column A. That's 4 zeroes.

Now in A1 number your first CD manually. In A2 enter this formula and drag
down

=IF(B2="","",IF(B2=B1,A1,A1+1))


--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Jacob Skaria]

Combine ColB & ColD and check with the previous entry..If same increment from
top cell or else 1.

=IF(B2="","",IF(B2&D2=B1&D1,A1+1,1))

'format cell to 0000

 

[Dave Peterson]

If you've ripped the cd to your harddrive and you used a track number in the
title of the track, you may want to look at John Walkenbach's MP3 file lister
workbook:

http://spreadsheetpage.com/index.php/blog/mp3_file_lister_posted/

 

[Luke M]

Assuming your info is sorted by artist and CD name:
=IF(AND(B2=B1,D2=D1),A1,A1+1)