"Serial numbering" \ Numbering upon chronological order

[alon]

Hello dear forum members,

I have a large data set for sale dates and times of various products.
I would like to add a column, that for each different type of product (product SKUs provided), would number the sales chronologically. By this I mean the earliest sale of each product would get "1", the sale after that one would get "2", and so on, from old to new, for each and every product in the sheet. This would of course be easy if there was only one product... however that's not the case...

I have uploaded a small sample of the data, for just 3 products, alongside what I hope to achieve. Here it is:

http://dl.dropbox.com/u/7112203/questionbook.xlsx

By the way, I must again thank jonmo1 and teylyn, without whom I would not have had the dates formatted properly!

Thanks in advance!

 

[Firefly2012]

Hi

Assuming you have the data sorted ascending, then with SKUs in column A and dates in column B you could use a formula in Column C:

=countifs($a2,$a$2:$a2,$b2,$b$2:$B2)

Copied down. Note I can't download files from work so I have made assumptions.

 

[alon]

Thanks for the quick reply.
But... it's not working.
I have uploaded the file to google docs so you could see what it looks like
http://goo.gl/0lL5k

(obviously I replaced the Bs in your formula with Es)

 

[Firefly2012]

Hi Alon

Many apologies - I got the COUNTIF arguments round the worng way and had a mistake in there anyway (so wouldn't have worked). The following however should (doesn't require data to be sorted either):

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">SKU</td><td style=";">Date</td><td style=";">Count</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">BDC01</td><td style="text-align: right;;">15 December 2011</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">CDC01</td><td style="text-align: right;;">08 December 2011</td><td style="text-align: right;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">BDC01</td><td style="text-align: right;;">07 December 2011</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">BDC01</td><td style="text-align: right;;">23 December 2011</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">ADC01</td><td style="text-align: right;;">06 December 2011</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">ADC01</td><td style="text-align: right;;">22 December 2011</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">CDC01</td><td style="text-align: right;;">27 November 2011</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">CDC01</td><td style="text-align: right;;">26 December 2011</td><td style="text-align: right;;">5</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">DDC01</td><td style="text-align: right;;">13 December 2011</td><td style="text-align: right;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">BDC01</td><td style="text-align: right;;">16 December 2011</td><td style="text-align: right;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style=";">DDC01</td><td style="text-align: right;;">26 November 2011</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style=";">DDC01</td><td style="text-align: right;;">18 December 2011</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style=";">DDC01</td><td style="text-align: right;;">02 December 2011</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style=";">ADC01</td><td style="text-align: right;;">09 December 2011</td><td style="text-align: right;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style=";">CDC01</td><td style="text-align: right;;">05 January 2012</td><td style="text-align: right;;">7</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style=";">BDC01</td><td style="text-align: right;;">25 December 2011</td><td style="text-align: right;;">5</td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style=";">ADC01</td><td style="text-align: right;;">24 November 2011</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style=";">DDC01</td><td style="text-align: right;;">27 December 2011</td><td style="text-align: right;;">5</td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style=";">ADC01</td><td style="text-align: right;;">27 December 2011</td><td style="text-align: right;;">5</td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style=";">CDC01</td><td style="text-align: right;;">06 December 2011</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style=";">CDC01</td><td style="text-align: right;;">09 December 2011</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style=";">CDC01</td><td style="text-align: right;;">30 December 2011</td><td style="text-align: right;;">6</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet3</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C2</th><td style="text-align:left">=COUNTIFS(<font color="Blue">$A:$A,$A2,$B:$B,"<="&$B2</font>)</td></tr></tbody></table></td></tr></table><br />

 

[alon]

NEAT! Seems to be working! Thanks a lot FireFly! :D