Assign ID number to blank address entries

[tamboy]

Hello,

I was given a file to clean up. It has over 15,000 address entries in a row format:

[TABLE="width: 192"]
<tbody>[TR]
[TD="width: 64"]ID[/TD]
[TD="width: 64"]Agency[/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD="align: right"]201[/TD]
[TD]Name[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]Address 1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]City,State, Zip[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Phone #[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

There is an id number assigned in the cell in column A at the Agency name. All the address fields start in column B. The ID # is only assigned to the agency name.

A problem is that not all entries have each of the address fields. There might only be an agency with a phone # or agency,address 1,city/state/zip and no phone #.

Is there any code or formula that can assign the id code of agency to each of its respective address entries? So it looks like this:

A B
201 Agency
201 Address 1
201 City, State Zip
201 Phone Number

Thanks

Tamboy

 

[Fluff]

Hi & welcome to MrExcel
Select the ID column > Ctrl G > Special > Blanks > Ok > type = followed by Up arrow > Ctrl Enter

 

[tamboy]

Thank you but that only works, at least how I'm doing this, is to manually enter the sequence at each address entry. There are 15,000 of them.

[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]ID[/TD]
[TD="width: 64"]Agency[/TD]
[/TR]
[TR]
[TD="align: right"]201[/TD]
[TD]Name[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Address 1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]City,State, Zip[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Phone #[/TD]
[/TR]
[TR]
[TD="align: right"]301[/TD]
[TD]Name[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Address 1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]City,State, Zip[/TD]
[/TR]
[TR]
[TD="align: right"]401[/TD]
[TD]Name[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Phone #[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

If you select the entire column & follow the instructions it should fill all blank cells with the relevant ID.
Is that not happening.

PS when typing the = & up arrow do not select any cells first.

 

[tamboy]

Totally awesome, it was user error and now it works. You saved me days of hand entry and weeping.

 

[Fluff]

Glad to help & thanks for the feedback
You can also do that with VBA like

Sub FillBlanks()
With Range("A:A")
   .SpecialCells(xlBlanks).FormulaR1C1 = "=r[-1]c"
   .Value = .Value
End With
End Sub

 

[tamboy]

Even better, thank you thank you.

Tamboy

 

[Fluff]

You're welcome