Entering repeat data

  • Thread starter Daniel - Sydney
  • Start date
D

Daniel - Sydney

Hi

Using Excel 2000 SP3.
I have a spreadsheet with two columns about 2,500 cells long.
A2 shows the date of the first purchase and B2 shows the cost,
B3 to B5 (say) show the cost of each transaction on that day but the date is
not repeated in A.
A6 shows the date of the first transaction on that date and B6 the cost
and so on, there is no consistancy in the number of transactions per day.
I need to fill in the date of each transaction and dont want to do it
manually
by dragging the cell down as I have over 250 seperate dates on the
spreadsheet.

Is there a way to automate this?

thanks

Daniel
 
G

Gary''s Student

We will use a very simple formula and a helper column
1. format column C as Date
2. In C2 enter =A2
3. In C3 enter =IF(A3="",C2,A3)
4. Copy this all the way down

You will see, at this point, exactly want you want in column A.

Select all of column C, copy and paste as value back into column A and you
are done!
 
G

Govind

Hi,

Add another helper column (say in Column C), and enter this formula
there in cell C2

=IF(A2<>"",A2,C1) and copy this down to the range you need.

If you dont want the Column A, then copy column C and paste it in Column
A by choosing Edit->Paste Special->Values.

Regards

Govind.
 
D

Daniel - Sydney

Great, thanks for your help,
it gave me exactly what I needed.

regards

Daniel
 
B

Bryan Hessey

Daniel,

Insert a new column B and enter in B2

=if(A2<>"",A2,B1)

and drag that down to the end of your data, - then Copy the column and
Paste special = Values.
then delete the original column A
 
Top