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! :D

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


"diablo9" wrote in message
...
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! :D

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

HTH,
Bernie
MS Excel MVP


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
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


"diablo9" wrote in message
...
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! :D

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

You are the man!


"Bernie Deitrick" wrote:

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


"diablo9" wrote in message
...
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! :D