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

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

Gary''s Student


"Daniel - Sydney" wrote:

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

You're trying to clean up existing data by filling the empty cells with the
value above?

If yes, visit Debra Dalgleish's site:
http://contextures.com/xlDataEntry02.html

She has some manual techniques and VBA code. (Only doing it once, the manual
way is quicker!)

Daniel - Sydney wrote:

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

--

Dave Peterson

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.

Daniel - Sydney wrote:

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

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

regards

Daniel

"Gary''s Student" wrote:

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

Gary''s Student


"Daniel - Sydney" wrote:

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

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



Daniel - Sydney Wrote:
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


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=402019