Copy values in blank cell in row from single value in second column

[earthworm]

I have a huge data set from which what i want is

i want all the values from column b to column a . but the condition is instead of single value for the series / category i want all the list to be appeared in colum a from colum b instead of doing it manually . i want to achieve this because i want to apply sumif formula if the condition is met .

the main goal is

look at the value next to cell from colum b and insert the value in below rows till the next value changes in column b and so on

sample sheet attached .

yellow highligted is the one i want in such a way from column b

Please advice !!!

http://www.box.net/shared/7rgq1akdia

 

[VoG]

Try

Sub FillDown()
Dim LR As Long
LR = Range("B" & Rows.Count).End(xlUp).Row
Range("A1:A" & LR).Value = Range("B1:B" & LR).Value
With Range("A1:A" & LR)
    With .SpecialCells(xlCellTypeBlanks)
        .FormulaR1C1 = "=R[-1]C"
    End With
    .Value = .Value
End With
End Sub

 

[mirabeau]

Just a comment.

If your "huge" data set has more than about 40,000 rows then you may have problems with the Special Cells approach.

The following code should work quite happily up to a million or more rows, and may be a bit faster than special cells even where both approaches work.

Sub fillin()
Dim e As Range
Set e = Range("B1")
Do
If e(2) = "" Then
    Range(e, e.End(4)(0)).Offset(, -1) = e.Value
    Set e = e.End(4)
Else:
    e.Offset(, -1) = e.Value
    Set e = e(2)
End If
If e.End(4).Row = Rows.Count Then e.Offset(, -1) = e.Value: Exit Do
Loop
End Sub

 

[earthworm]

Thanks . it worked correctly. is there any formula method ?

 

[earthworm]

Just a comment.

If your "huge" data set has more than about 40,000 rows then you may have problems with the Special Cells approach.

The following code should work quite happily up to a million or more rows, and may be a bit faster than special cells even where both approaches work.

Sub fillin()
Dim e As Range
Set e = Range("B1")
Do
If e(2) = "" Then
    Range(e, e.End(4)(0)).Offset(, -1) = e.Value
    Set e = e.End(4)
Else:
    e.Offset(, -1) = e.Value
    Set e = e(2)
End If
If e.End(4).Row = Rows.Count Then e.Offset(, -1) = e.Value: Exit Do
Loop
End Sub

i willl prefer formula method, because if i want to do the same somewhere else i dont know the vba ! thats y . comon there should be some way to do this with formula . anyone ?????

 

[VoG]

Copy column B to column A.

Select Column A, press F5, click Special, tick Blanks, click OK.

Press =

Press Up arrow

Hold down CTRL and press Enter.

 

[earthworm]

Copy column B to column A.

Select Column A, press F5, click Special, tick Blanks, click OK.

Press =

Press Up arrow

Hold down CTRL and press Enter.

greattttttt !!! that worked perfectly and soo easy !

i didnt knew it was that simple to achieve !

thanks !! you rock !