Set message subject here ...
in [General]
|
From: Cal on 24 May 2010 04:41 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? -- --------------------------------- --- -- - Posted with NewsLeecher v3.9 Final Web @ http://www.newsleecher.com/?usenet ------------------- ----- ---- -- -
From: ck13 on 24 May 2010 05:28 Hi, See if this works =IF(SUMPRODUCT(--(B2=B1),--(D2=D1))=1,A1,A1+1) "Cal" wrote: > 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? > > > > > > -- > --------------------------------- --- -- - > Posted with NewsLeecher v3.9 Final > Web @ http://www.newsleecher.com/?usenet > ------------------- ----- ---- -- - > > . >
From: ck13 on 24 May 2010 05:32 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#") "ck13" wrote: > Hi, > > See if this works =IF(SUMPRODUCT(--(B2=B1),--(D2=D1))=1,A1,A1+1) > > "Cal" wrote: > > > 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? > > > > > > > > > > > > -- > > --------------------------------- --- -- - > > Posted with NewsLeecher v3.9 Final > > Web @ http://www.newsleecher.com/?usenet > > ------------------- ----- ---- -- - > > > > . > >
From: Mike H on 24 May 2010 05:34 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. "Cal" wrote: > 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? > > > > > > -- > --------------------------------- --- -- - > Posted with NewsLeecher v3.9 Final > Web @ http://www.newsleecher.com/?usenet > ------------------- ----- ---- -- - > > . >
From: Jacob Skaria on 24 May 2010 05:59
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 -- Jacob (MVP - Excel) "Cal" wrote: > 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? > > > > > > -- > --------------------------------- --- -- - > Posted with NewsLeecher v3.9 Final > Web @ http://www.newsleecher.com/?usenet > ------------------- ----- ---- -- - > > . > |