I have a list of names in alphabetical order that occur in one column with
about 20 blank lines between each consecutive name.

Is there a tool in excel that will auto populate the cells directly below
each name with out having to copy/paste each. (there can be up to 500 names)
I tried to set up a macro but no luck.

One way is to (assuming your list is in Col A)
type
=A1 in B1
Type
=IF(A2="",B1,A2) in B2 and copy down till the last row

Copy Col B and PASTE SPEICAL|VALUE over Col A
Delete Col B

"khuff" wrote:

I have a list of names in alphabetical order that occur in one column with
about 20 blank lines between each consecutive name.

Is there a tool in excel that will auto populate the cells directly below
each name with out having to copy/paste each. (there can be up to 500 names)
I tried to set up a macro but no luck.

Select the column and F5SpecialBlanksOK

Type an = sign in active blank cell then point or arrow to cell above.

CTRL + ENTER to fill the blank cells.

Copy/paste special.values/ok/esc


Gord Dibben MS Excel MVP

On Wed, 29 Oct 2008 08:40:06 -0700, khuff
wrote:

I have a list of names in alphabetical order that occur in one column with
about 20 blank lines between each consecutive name.

Is there a tool in excel that will auto populate the cells directly below
each name with out having to copy/paste each. (there can be up to 500 names)
I tried to set up a macro but no luck.

Hi,

You should reconsider Gord's suggestion, it is the method of choice for
solving these types of problems and it will work on multiple columns. You
should give Gord's solution a check Yes.

--
Cheers,
Shane Devenshire


"khuff" wrote:

I have a list of names in alphabetical order that occur in one column with
about 20 blank lines between each consecutive name.

Is there a tool in excel that will auto populate the cells directly below
each name with out having to copy/paste each. (there can be up to 500 names)
I tried to set up a macro but no luck.

Hey Gord,

I used the formula Sheeloo gave me and it worked great although I had to
pull the fill tool for awhile before I was finished.

As Shane says your suggestion seems to be the preferred method and I will be
trying it the next time I download and convert one of these documents I have
to work with. Thanks to all of you for the help and sorry for taking so long
to reply.



"Gord Dibben" wrote:

Select the column and F5SpecialBlanksOK

Type an = sign in active blank cell then point or arrow to cell above.

CTRL + ENTER to fill the blank cells.

Copy/paste special.values/ok/esc


Gord Dibben MS Excel MVP

On Wed, 29 Oct 2008 08:40:06 -0700, khuff
wrote:

I have a list of names in alphabetical order that occur in one column with
about 20 blank lines between each consecutive name.

Is there a tool in excel that will auto populate the cells directly below
each name with out having to copy/paste each. (there can be up to 500 names)
I tried to set up a macro but no luck.

Works like a champ! Wish I would have asked about this a long time ago.

Thanks Again

"ShaneDevenshire" wrote:

Hi,

You should reconsider Gord's suggestion, it is the method of choice for
solving these types of problems and it will work on multiple columns. You
should give Gord's solution a check Yes.

--
Cheers,
Shane Devenshire


"khuff" wrote:

I have a list of names in alphabetical order that occur in one column with
about 20 blank lines between each consecutive name.

Is there a tool in excel that will auto populate the cells directly below
each name with out having to copy/paste each. (there can be up to 500 names)
I tried to set up a macro but no luck.