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

 

[sandy666]

using Power Query aka Get&Transform

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]ORG[/td][td=bgcolor:#5B9BD5]Services[/td][td][/td][td=bgcolor:#70AD47]ORG[/td][td=bgcolor:#70AD47]Services[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]ABC Org[/td][td=bgcolor:#DDEBF7]Assist Prod-Pers Care/Safety[/td][td][/td][td=bgcolor:#E2EFDA]ABC Org[/td][td=bgcolor:#E2EFDA]Assist Prod-Pers Care/Safety[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]Disability Safety[/td][td][/td][td]ABC Org[/td][td]Disability Safety[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Whole Family Health[/td][td=bgcolor:#DDEBF7]Interpret/Translate[/td][td][/td][td=bgcolor:#E2EFDA]Whole Family Health[/td][td=bgcolor:#E2EFDA]Interpret/Translate[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]Assist Prod-Pers Care/Safety[/td][td][/td][td]Whole Family Health[/td][td]Assist Prod-Pers Care/Safety[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]Personal Mobility Equipment[/td][td][/td][td=bgcolor:#E2EFDA]Whole Family Health[/td][td=bgcolor:#E2EFDA]Personal Mobility Equipment[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]Early Childhood Supports[/td][td][/td][td]Whole Family Health[/td][td]Early Childhood Supports[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Visability Ltd[/td][td=bgcolor:#DDEBF7]Service1[/td][td][/td][td=bgcolor:#E2EFDA]Visability Ltd[/td][td=bgcolor:#E2EFDA]Service1[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]Service2[/td][td][/td][td]Visability Ltd[/td][td]Service2[/td][/tr]
[/table]

[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    FilledDown = Table.FillDown(Source,{"ORG"})
in
    FilledDown[/SIZE]

 

[Blessy Clara]

Hi sandy666

Thank you for your response. Sorry, I am not aware of PowerQuery. Do I have to use the code you have given me in VBA?

 

[sandy666]

no, this is M-code not vba

Data tab - New Query - From Other Sources - Blank Query
it will open Power Query Editor
Advanced Editor - replace code there with code copied from the post then OK, Close&Load


be sure the name in the code is the same as your source table name (here: Table1)

 

[mohadin]

Hi
What about

Sub test()
    Dim a As Variant
        a = Cells(1, 1).Resize(Cells(Rows.Count, 2).End(xlUp).Row, 2)
    For i = 2 To UBound(a)
    If a(i, 1) = "" Then a(i, 1) = a(i - 1, 1)
     Next
     Cells(1, 1).Offset(1, 5).Resize(UBound(a), 2) = a
End Sub

 

[Fluff]

How about

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

 

[Blessy Clara]

Hi Sandy66 Thank you - but i am unable to get Blank Query as mentioned (post following the steps) . However thanks for your time and response

 

[sandy666]

what is your Excel version?

 

[Blessy Clara]

Hi Mohadin
Thank you very much for the Code, Yes it was indeed very helpful

 

[Blessy Clara]

Hi Fluff
Thank you very much for the code. However I am getting a runtime error 1004 when I used it