Labeling data

[Slim Slender]

In col A is a continuous list of dates for every day for several
years. In col B are periodic entries next to some of the dates in A.
For each item in col B, I need to make an entry in col C that looks
like ST001 next to the first one, ST002 next to the second one, ST003,
etc. The only other condition is that the numbering starts at a
specific place, a specific item in B, 10/29/2004, which is not the
first item in B.
I tried just filtering for non-blanks in B and then dragging down C
but the entries repeat or don't increment. I tried creating a list of
the labels in another place and pasting it next to the filtered non-
blanks but those fall into the "cracks" of the hidden rows. So, it
appears the only thing for it is a bit of code. Any help would be much
appreaciated.

 

[Lars-Åke Aspelin]

In col A is a continuous list of dates for every day for several
years. In col B are periodic entries next to some of the dates in A.
For each item in col B, I need to make an entry in col C that looks
like ST001 next to the first one, ST002 next to the second one, ST003,
etc. The only other condition is that the numbering starts at a
specific place, a specific item in B, 10/29/2004, which is not the
first item in B.
I tried just filtering for non-blanks in B and then dragging down C
but the entries repeat or don't increment. I tried creating a list of
the labels in another place and pasting it next to the filtered non-
blanks but those fall into the "cracks" of the hidden rows. So, it
appears the only thing for it is a bit of code. Any help would be much
appreaciated.

Try the following formula in cell C1:

=IF(AND(A1>=DATE(2004,10,29),B1<>""),"ST"&TEXT(SUMPRODUCT((A$1:A1>=DATE(2004,10,29))*(B$1:B1<>"")),"000"),"")

Copy this down as far as you have data in columns A and B.

Hope this helps / Lars-Åke