how to autofill a consecutive cells in a column?

[Guest]

This problem has bugged me for a long time. Please help!
I have column A with 2, 4, 6, 8, ....100, which represents the time line in
days. Column B with 0, 0, 1, 0, 3, 2, ...5, 3... which represents a certain
event that happend in that day. Let's say totally the event happened 300
times in the 100 days.
I would like to have a column C, which has 300 cells corresponding to those
300 events. If the first event happened once in day 6, then put a "6" into
first cell of column C. If the next event happened 3 times in day 10, then
put 10, 10 ,10, into 2nd, 3rd, 4th cell of column C. So finally I have 300
cells in column C, each cell representing the day on which the corresponding
event happened.
I really wish I made it clear. Please help!

 

[Bernie Deitrick]

D,

You need to use two colummns of formulas.

Let's say that your headers are in row 1, and your values start in row 2, with times in column A and
counts in column B.

In cell C2, enter
=B2

In cell C3, enter this
=C2+B3

and copy down to match your time line.

Then in cell D2, enter

=INDEX(A:A,IF(ISERROR(MATCH(ROW()-1,C:C,FALSE)),MATCH(ROW()-1,C:C)+1,MATCH(ROW()-1,C:C,FALSE)))

and copy down to match your data table.

HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

Sorry. I should have said to copy the last formula down for 300 rows....

HTH,
Bernie
MS Excel MVP

 

[Guest]

thank a loooooooooottttttt!!!!!! Bernie, you saved me days of repetitive
work!!!!

You are the man!