Fill Data - Row Based

[Blessy Clara]

Hi I have an excel sheet with two columns
[TABLE="width: 500"]
<tbody>[TR]
[TD]ABC Org[/TD]
[TD]Assist Prod-Pers Care/Safety[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Disability Safety[/TD]
[/TR]
[TR]
[TD]Whole Family Health[/TD]
[TD]Interpret/Translate[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Assist Prod-Pers Care/Safety[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Personal Mobility Equipment[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Early Childhood Supports[/TD]
[/TR]
[TR]
[TD][TABLE="width: 302"]
<tbody>[TR]
[TD="width: 302"]Visability Ltd[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Service1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Service2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Actually the Blank Rows in Org Column Must be filled as this - Coz the Services are given as separate Row, it actually belongs to the Org occurring first and the next org and its services ..and so on

[TABLE="width: 500"]
<tbody>[TR]
[TD]ABC Org[/TD]
[TD]Assist Prod-Pers Care/Safety[/TD]
[/TR]
[TR]
[TD]ABC Org[/TD]
[TD]Disability Safety[/TD]
[/TR]
[TR]
[TD]Whole Family Health[/TD]
[TD]Interpret/Translate[/TD]
[/TR]
[TR]
[TD]Whole Family Health[/TD]
[TD]Assist Prod-Pers Care/Safety[/TD]
[/TR]
[TR]
[TD]Whole Family Health[/TD]
[TD]Personal Mobility Equipment[/TD]
[/TR]
[TR]
[TD]Whole Family Health[/TD]
[TD]Early Childhood Supports[/TD]
[/TR]
[TR]
[TD]Visability Ltd[/TD]
[TD]Service1[/TD]
[/TR]
[TR]
[TD]Visability Ltd[/TD]
[TD]Service2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thank you

 

[Blessy Clara]

Hi sandy666 it Version 14.0 MS Office 2010

 

[sandy666]

so you'll need Power Query add-in from MS site (for free )

 

[sandy666]

here is Power Query add-in, choose proper version

 

[mohadin]

You are very well come
Thank you for the feedback
Be happy

 

[Fluff]

There were a couple of typo's in the code

Sub Blessy()
    With Range("A2:A" & Range("B" & Rows.Count).End(xlUp).Row)
        .SpecialCells(xlBlanks).FormulaR1C1 = "=r[-1]c"
        .Value = .Value
    End With
End Sub