Fill down text

euanh

New Member
Joined
Jan 7, 2004
Messages
20
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
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

New Member
Joined
Jan 7, 2004
Messages
20
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Euan

So what do you want to copy and to where?

Did you try what Andrew or I suggested?
 

euanh

New Member
Joined
Jan 7, 2004
Messages
20
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
Euan

Did you actually try what was suggested?

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

euanh

New Member
Joined
Jan 7, 2004
Messages
20
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
 

Forum statistics

Threads
1,136,595
Messages
5,676,707
Members
419,644
Latest member
KeelsM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top