Fill-in empty cells from SAP report

[Guest]

When extracted to Excel, many reports from SAP do not come in a clean table
format but they appear with blank cells.

For example:
Column A Column B Column C Column D

2200352479 UB Plant 1311
Item1 75,00 EA
Item2 10,75 EA
2500045255 VB Plant 1511
Item1 25,00 EA
Item2 15,75 EA
Item3 10,50 EA

Is there a way to fill-in the blank cells below the numbers in Column A so
afterward I can make a pivot table? This is the desired result:

2200352479 UB Plant 1311
2200352479 Item1 75,00 EA
2200352479 Item2 10,75 EA
2500045255 VB Plant 1511
2500045255 Item1 25,00 EA
2500045255 Item2 15,75 EA
2500045255 Item3 10,50 EA

Many thanks in advance.

 

[Max]

Try Debra's page at:
http://www.contextures.com/xlDataEntry02.html

Fill Blank Cells
Fill Blank Cells Programmatically

 

[Richard Buttrey]

When extracted to Excel, many reports from SAP do not come in a clean table
format but they appear with blank cells.

For example:
Column A Column B Column C Column D

2200352479 UB Plant 1311
Item1 75,00 EA
Item2 10,75 EA
2500045255 VB Plant 1511
Item1 25,00 EA
Item2 15,75 EA
Item3 10,50 EA

Is there a way to fill-in the blank cells below the numbers in Column A so
afterward I can make a pivot table? This is the desired result:

2200352479 UB Plant 1311
2200352479 Item1 75,00 EA
2200352479 Item2 10,75 EA
2500045255 VB Plant 1511
2500045255 Item1 25,00 EA
2500045255 Item2 15,75 EA
2500045255 Item3 10,50 EA

Many thanks in advance.

Not with a function, but you could easily do it with a VBA macro.
The technique would be to loop down all the cells in column A, testing
for a gap and filling it with the value recorded in the variable set
when the macro last found an entry in column A.

Assumes the first line of data is in A3

Sub AddIDNumbers()
Dim stID As String
Dim x As Integer

x = Range(Range("B3"), Range("B63356").End(xlUp)).Rows.Count
stID = Range("A3")

For x = 1 To x
If Range("a3").Offset(x - 1, 0) = "" Then
Range("a3").Offset(x - 1, 0) = stID
Else: stID = Range("a3").Offset(x - 1, 0)
End If
Next

End Sub


HTH

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

 

[Markus L]

When extracted to Excel, many reports from SAP do not come in a clean table
format but they appear with blank cells.

For example:
Column A Column B Column C Column D

2200352479 UB Plant 1311
Item1 75,00 EA
Item2 10,75 EA
2500045255 VB Plant 1511
Item1 25,00 EA
Item2 15,75 EA
Item3 10,50 EA

Is there a way to fill-in the blank cells below the numbers in Column A so
afterward I can make a pivot table? This is the desired result:

2200352479 UB Plant 1311
2200352479 Item1 75,00 EA
2200352479 Item2 10,75 EA
2500045255 VB Plant 1511
2500045255 Item1 25,00 EA
2500045255 Item2 15,75 EA
2500045255 Item3 10,50 EA

Many thanks in advance.

http://www.contextures.com/xlDataEntry02.html