Copying different repeating entries down a column

[Guest]

Hi all,

I have an Excel spreadsheet with a layout similar to the following:
A1 = 'Dr. Johnson'
A2 = (blank)
A3 = (blank)
A4 = (blank)
A5 = 'Dr. Deperi'
A6 = (blank)
A7 = (blank)
A8 = 'Dr Marise'
etc. etc. etc.

For better use of pivot tables & filtering, I would like to copy the
existing names into their corresponding blank cells, which happen to appear
directly below the name. So, using the above example, what I am trying to
find is a quick method that would give me the following result:

A1 = 'Dr. Johnson'
A2 = 'Dr. Johnson'
A3 = 'Dr. Johnson'
A4 = 'Dr. Johnson'
A5 = 'Dr. Deperi'
A6 = 'Dr. Deperi'
A7 = 'Dr. Deperi'
A8 = 'Dr Marise'
etc. etc.

I have over 2000 rows to populate. Can anyone help?

Thanks!
Joe

 

[Biff]

Hi!

Use a temporary helper column.

Formula in B1:

=A1

Formula in B2:

=IF(A2="",B1,A2)

Copy down to the end of the data range in column A.

Then you can convert these formulas to constants and delete the original
column A if desired.

Biff

 

[Guest]

BIff,
Thanks, this works great.

Joe

Hi!

Use a temporary helper column.

Formula in B1:

=A1

Formula in B2:

=IF(A2="",B1,A2)

Copy down to the end of the data range in column A.

Then you can convert these formulas to constants and delete the original
column A if desired.

Biff