Fill Data - Row Based

[Blessy Clara]

Hi I have an excel sheet with two columns

ABC Org	Assist Prod-Pers Care/Safety
	Disability Safety
Whole Family Health	Interpret/Translate
	Assist Prod-Pers Care/Safety
	Personal Mobility Equipment
	Early Childhood Supports
Visability Ltd <tbody> </tbody>	Service1
	Service2

<tbody>
</tbody>

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

ABC Org	Assist Prod-Pers Care/Safety
ABC Org	Disability Safety
Whole Family Health	Interpret/Translate
Whole Family Health	Assist Prod-Pers Care/Safety
Whole Family Health	Personal Mobility Equipment
Whole Family Health	Early Childhood Supports
Visability Ltd	Service1
Visability Ltd	Service2

<tbody>
</tbody>

Thank you

 

[sandy666]

using Power Query aka Get&Transform

ORG	Services		ORG	Services
ABC Org	Assist Prod-Pers Care/Safety		ABC Org	Assist Prod-Pers Care/Safety
	Disability Safety		ABC Org	Disability Safety
Whole Family Health	Interpret/Translate		Whole Family Health	Interpret/Translate
	Assist Prod-Pers Care/Safety		Whole Family Health	Assist Prod-Pers Care/Safety
	Personal Mobility Equipment		Whole Family Health	Personal Mobility Equipment
	Early Childhood Supports		Whole Family Health	Early Childhood Supports
Visability Ltd	Service1		Visability Ltd	Service1
	Service2		Visability Ltd	Service2

[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