Copying different repeating entries down a column

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

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

"Access Joe" <[email protected]> wrote in message
news:[email protected]...
> 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,
Thanks, this works great.

Joe

"Biff" wrote:

> 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
>
> "Access Joe" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
>
>
>