index (count? sequence?) unique dates based on values in another column

[William Judd]

I have 34,545 rows of date information related to 522 products. I want to add a column that shows the week number for each row, starting from the earliest date associated with each product, through the last date. Some rows have the same date for a single product.
So I need to transform this:
Product Sell Date Week of Campaign
Widget A 09-Apr-2011
Widget C 07-May-2011
Widget A 16-Apr-2011
Widget C 30-Apr-2011
Widget B 16-Apr-2011
Widget B 14-May-2011
Widget C 14-May-2011
Widget B 30-Apr-2011
Widget A 16-Apr-2011
Widget A 07-May-2011
Widget B 23-Apr-2011
Widget B 16-Apr-2011
Widget B 07-May-2011
Widget A 16-Apr-2011
Widget B 14-May-2011
Widget C 07-May-2011
Widget A 23-Apr-2011
Widget B 09-Apr-2011
Widget A 30-Apr-2011

Into this:

Product Sell Date Week of Campaign
Widget A 09-Apr-2011 1
Widget A 16-Apr-2011 2
Widget A 16-Apr-2011 2
Widget A 16-Apr-2011 2
Widget A 23-Apr-2011 3
Widget A 30-Apr-2011 4
Widget A 07-May-2011 5
Widget B 09-Apr-2011 1
Widget B 16-Apr-2011 2
Widget B 16-Apr-2011 2
Widget B 23-Apr-2011 3
Widget B 30-Apr-2011 4
Widget B 07-May-2011 5
Widget B 14-May-2011 6
Widget B 14-May-2011 6
Widget C 30-Apr-2011 1
Widget C 07-May-2011 2
Widget C 07-May-2011 2
Widget C 14-May-2011 3

I really appreciate any help, even reference to a formula name.
Thanks!
--William

[protonLeah]

Your post belongs in one of the Help Request forums, however...
Does this work?

[Marcol]

If this is a one off exercise, go with protonLeah.

If the workbook is constantly updated then this might help.

This workbook uses non-volatile dynamic named ranges, this might cause your actual workbook (34k+ rows) to take longer to open, but once opened it will greatly out perform volatile names.
The helper column that concatenates Columns A&B, then removes duplicates, is hidden with the grouping button

Add to Columns A:C as required, keep the data in Column A continuous i.e. no blanks.

Note
Some of your dates have times included this could lead to inconsistancies in the returned values, hence the use of INT() in the formulae.

[William Judd]

Thank you, protonLeah. I appreciate your quick assistance very much! I see now that my original request was badly worded (in addition to being posted in the wrong place!).
What I need is not the "Week Number" counted from Jan 1, but the week number counted from the first instance of each widget. Marcol's suggestion does the same thing, thanks to my misleading request. So sorry about that.

For each record that belongs to widget A, I want to check the date to know if that record belongs to the first week of the campaign, the second week of the campaign, and so on. 552 campaigns, each with its own start date and each with some number of records between 2 and about 100.

I really really appreciate your efforts!
--William

[William Judd]

Thank you, Marcol. Sorry about my misleading original request (and it was posted in the wrong place!).

What I need is not the "Week Number" counted from Jan 1, but the week number counted from the first instance of each widget.

For each record that belongs to widget A, I want to check the date to know if that record belongs to the first week of the campaign, the second week of the campaign, and so on. 552 campaigns, each with its own start date and each with some number of records between 2 and about 100.

I so very much appreciate your efforts!
--William

[dangelor]

Try this code...

Please Login or Register  to view this content.

[William Judd]

Like magic! Thank you very much - it is awesome.

[Marcol]

See if Sheet2 of this workbook might be a formula solution.

[William Judd]

Thank you, sir. This combination of formulae approaches what I need and is indeed nifty. But I need to use the new column "campaign week #" alongside the original data in order to generate more reports/piviots. So I can't use a solution that summarizes the data. For each and every original row, I need one ordinal "campaign week #" even if there are duplicates.

Is there a formulaic method for that?

Cheers,
--William

[dangelor]

You are welcome!

[daddylonglegs]

assuming you sort your data so that it is sorted by column A then by column B (oldest to newest) then you can use this formula in C2 copied down to get the week number

=INT((INT(B2)-INT(VLOOKUP(A2,A:B,2,0)))/7+1)

see attached

[William Judd]

Thank you Daddy Longlegs! Spiffing. Thank you for listening and making (hope my Latin's not too rusty there).
One more operation I want to perform is to identify the first date and last date of each campaign. This could result in a list of the 522 unique campaigns with their start and end dates. I haven't been able to figure out a formula that will do that, though.
Cheers,
--William