macro

[EricBB]

below is my original data:

ID Chap SubChap Class SubClass Unit Des1 Des2
2 Site
Subsurf
Subcon
Excavation LS
Backfill LS
3 Concrete
Formwork
Plywood
Marine pc Origin Thick
Brazil 4 mm
China 6 mm
Reinf.
Steel
Coated ton Origin Dia.
Qatar 4 mm
Oman 6 mm
8 mm
Uncoated ton Origin Dia.
Qatar 4 mm
Oman 6 mm
8 mm

what i want is a macro to make the above data same as below.

ID Chap SubChap Class SubClass Unit Des1 Des2
2 Site Subsurf Subcon Excavation LS
2 Site Subsurf Subcon Backfill LS
3 Concrete Formwork Plywood Marine piece Brazil 4 mm
3 Concrete Formwork Plywood Marine piece Brazil 6 mm
3 Concrete Formwork Plywood Marine piece China 4 mm
3 Concrete Formwork Plywood Marine piece China 6 mm
3 Concrete Reinf. Steel Coated ton Qatar 4 mm
3 Concrete Reinf. Steel Coated ton Qatar 6 mm
3 Concrete Reinf. Steel Coated ton Qatar 8 mm
3 Concrete Reinf. Steel Coated ton Oman 4 mm
3 Concrete Reinf. Steel Coated ton Oman 6 mm
3 Concrete Reinf. Steel Coated ton Oman 8 mm
3 Concrete Reinf. Steel Uncoated ton Qatar 4 mm
3 Concrete Reinf. Steel Uncoated ton Qatar 6 mm
3 Concrete Reinf. Steel Uncoated ton Qatar 8 mm
3 Concrete Reinf. Steel Uncoated ton Oman 4 mm
3 Concrete Reinf. Steel Uncoated ton Oman 6 mm
3 Concrete Reinf. Steel Uncoated ton Oman 8 mm

appreciate any help..

 

[Jacob Skaria]

You can update this without using a macro.. Suppose column J is ununsed. Type
in a formula '= J1' in cell J2 and copy the cell.

Select your data range..Press F5 from 'Goto window' select Blanks. and OK.
This will select all blank cells. Right click.. Paste special..Formulas....

If this post helps click Yes

 

[Jacob Skaria]

Please try this ...

Sub Macro1()
For Each cell In ActiveSheet.UsedRange
If cell.Row > 1 Then
Debug.Print cell.Address
If Trim(cell.Value) = "" Then cell.Value = Cells(cell.Row - 1,
cell.Column).Text
End If
Next
End Sub

If this post helps click Yes

 

[EricBB]

thank you for your reply. but what i want is to repeat the process as per the
number of items in desc-2.
below is the link to my file
http://www.savefile.com/files/2094166

 

[Jacob Skaria]

This should work if you select the Entire range of data before pressing (F5).

OR else

Try the macro

If this post helps click Yes

 

[EricBB]

if you can notice the desc-1 & desc-2 columns below:
Desc-1 Desc-2
Origin Bar Diameter
Emirates 8 mm
Oman 10 mm
12 mm

it becomes,

Desc-1 Desc-2
Emirates 8 mm
Emirates 10 mm
Emirates 12 mm
Oman 8 mm
Oman 10 mm
Oman 12 mm

it added 3 rows by repeating the diameter by its origin. you can find those
in the "result sheet" from my uploaded file.

thanks for time.

 

[Jacob Skaria]

Dear Eric

In the below macro please adjust the last row and last column as per your
requirement.

Sub Macro()
lngLastRow = 20
lngLastCol = 2

For lngRow = 2 To lngLastRow
For lngCol = 1 To lngLastCol
If Trim(Cells(lngRow, lngCol)) = "" Then
Cells(lngRow, lngCol) = Cells(lngRow - 1, lngCol)
End If
Next
Next

End Sub

If this post helps click Yes

 

[Dave Peterson]

First, I didn't open the attachment. Too many bad things can be contained in
excel workbooks.

But I think you'll have to share some more rules of how things get translated.

I'm guessing that your data is in columns A:H (and nothing more???).

How did you know to ignore the cells with Origin in them--and the cells to the
right of that "origin" word? Are there other descriptions that should be
ignored?

If Backfill-LS had been Backfill-MS, would you have ended up with 4 rows of
output?

Are there certain columns that should never be repeated in the output--like ID
through SubClass?

I see how you came up with your output, but I don't think I could come up with a
macro without more rules.

 

[EricBB]

the macro fill only the blanks cells, it did not insert any additional rows
as per my previous sample..

extracted column from original data:

Desc-1 Desc-2
Origin Bar Diameter
Emirates 8 mm
Oman 10 mm
12 mm

the results should show something like in the below,

Desc-1 Desc-2
Emirates 8 mm
Emirates 10 mm
Emirates 12 mm
Oman 8 mm
Oman 10 mm
Oman 12 mm

if you will noticed, it added 3 rows by repeating the diameter by its origin
and the word "origin" & "diameter" was deleted.