Fill down text

[euanh]

I have a list of addresses and search types (property type searches such as local authority, water, environmental etc)

The problem I have is that is a property has say 3 searches associated with it the address appears against the first search and not the rest. The address is in one column and the searches are in the next column so the data isn't combined.

What I would like is either a formula or a macro that can scan down a column filling in the blank addresses using the first line until it finds a new address which t will then use to fill in the blanks below and so on. There could be several searches for each property so there isn't really any logic to use on the number of rows to fill before you hit the next proerty address.

45 Wells Court, Wellstone, ROCHESTER, Kent, ME44 2OR LLC1
Envirosearch Residential
Southern Water
11 Dogwood Road, Dogwood, Dorset, BH8 9PQ LLC1
Envirosearch Residential

Can anyone help ?

Thanks in advance
Euan

 

[Norie]

Euan

It's not clear from your sample data exactly what you mean or how your data is structured, but there is a way to fill in blanks in a column.

1. Select the column with the blanks.
2. Goto Edit>Goto...Special and select blanks.
3. Goto the formula bar, enter =A1 (or whatever the first cell with data is) and then press CTRL+ENTER.
4. Select the column, copy and paste special values.

 

[Andrew Poulsom]

If I understand you correctly, select the column you want to fill and choose Edit|Go To|Special, check Blanks and click OK. Type =, press the up arrow and press Ctrl+Enter (that's hold down Ctrl and press the Enter key).

 

[euanh]

Fill Text

Sorry guys, didn't realised I had pasted the sample data in. Tried to and thought it looked odd so wasn't going to confuse people, I obviously have.

Short of trying to suss out how to paste an image or sample date I will describe what it looks like.

45 Wells Court .... is in cell A1, LLC1 is in cell B1, Environmental Residential is in cell B2 and Southern Water is in cell B3

11 Dogwood Road ..... is in cell A4, LLC1 is in cell B4 and Environmental Residential is in cell B5

Hope this helps
Euan

 

[Norie]

Euan

So what do you want to copy and to where?

Did you try what Andrew or I suggested?

 

[euanh]

Fill down Text

Sorry not being clear.

I want cell a2 and a3 to be the 45 Wells Court address and then cell a5 to be the 11 Dogwood address. the macro needs to therefore recognise a cell has something in it and fill down the blanks below it until it finds another populated cell. It then needs to use the new data it has found and fill down below that etc etc.

Basically I want the address to appear next to each search as opposed to just appearing next to the first one ordered.

Thanks
Euan

PS did read you responses but not sure they are what I am looking for, thanks

 

[Norie]

Euan

Did you actually try what was suggested?

There should be no need for code to do what you want.

 

[euanh]

Sorry guys I am being v ignorant.

I have now tried Andrew's suggested solution and it works a treat. I had no idea that would work hence was a bit sceptical.

Thanks guys this will prove to be exceedingly useful.

Euan