Duplicating Cells of Data

[amroberts2]

I'd like to find a way to duplicate cells containing addresses and paste them into the empty cells below them, which are household members. The attached sample spreadsheet lays out my starting point. The result should replicate the address information for each household member.

Thanks.

[martindwilson]

assuming family members are together and a new one always has an address then
something like attached
once done copy the lot paste back special /values to remove the functions

[amroberts2]

Thanks Martin.

That worked great for the first line. I am not able to drag the formula through all streets though, otherwise I will replicate the first address through all cells. Is there an easy way to copy the formula through my cells? I may have hundreds of these cells to replicate.

Thanks again.

[amroberts2]

Hey Martin, I figured out the trick. I'm all set.

Thanks for the quick response and help to my Excel headache!