|
macro
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.. |
macro
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 "EricBB" wrote: 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.. |
macro
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 --------------- Jacob Skaria "EricBB" wrote: 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.. |
macro
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" wrote: 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 "EricBB" wrote: 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.. |
macro
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 --------------- Jacob Skaria "EricBB" wrote: 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" wrote: 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 "EricBB" wrote: 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.. |
macro
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" wrote: 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 --------------- Jacob Skaria "EricBB" wrote: 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" wrote: 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 "EricBB" wrote: 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.. |
macro
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 --------------- Jacob Skaria "EricBB" wrote: 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" wrote: 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 --------------- Jacob Skaria "EricBB" wrote: 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" wrote: 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 "EricBB" wrote: 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.. |
macro
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 wrote: 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.. -- Dave Peterson |
macro
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. "Jacob Skaria" wrote: 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 --------------- Jacob Skaria "EricBB" wrote: 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" wrote: 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 --------------- Jacob Skaria "EricBB" wrote: 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" wrote: 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 "EricBB" wrote: 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.. |
| All times are GMT +1. The time now is 06:02 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com