| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding rows
I have a file that I thought was in a great format for my application but it
turns out its not. Here is an example of what I have: 1 12.1 1 12.6 1 12.9 2 8.4 2 8.7 3 30.1 3 30.9 3 31.0 Column A is sample number and when it changes to a new sample number I need something that inserts 4 rows with the 1st row = UWI: 2nd row = Common: 3rd row = Curve: 4th row = Measr. Depth Below is what it should like but it needs to happen everytime it switches to a new sample #. UWI: Common: Curve: Measr. Depth 1 12.1 1 12.6 1 12.9 It would be great if it could also put the sample number in the UWI field so in the above example it would look like not necessary but would be nice. UWI: 1 Common: Curve: Measr. Depth 1 12.1 1 12.6 1 12.9 as always thanks for your help. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Adding rows
Don,
This is great!!! I have something like 20,000 rows and this has just saved me a lot of heartbreak. Can you help with an additional step that I think will help my format. Below is what the code did for me and its great. The cells below "Measr. Depth", need to be deleted and the data that is in column B and C need to move to column A and B, is this possible or is it asking to much from a code. UWI. 25025210790000 Common. Curve. PHIE Measr. Depth 25025210790000 8900.0 0.056111765 25025210790000 8900.2 0.059111765 25025210790000 8900.4 0.061111765 UWI. 25025210930000 Common. Curve. PHIE Measr. Depth 25025210930000 9350.0 0.05211 25025210930000 9350.2 0.05211 25025210930000 9350.4 0.05261 Thanks again "Don Guillett" wrote: Sub createtable() Application.ScreenUpdating = False mc = 1 '"a" For i = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1 If Cells(i - 1, mc) < Cells(i, mc) Then Rows(i).Resize(4).Insert 'MsgBox i Cells(i, mc) = "UWI:" & Cells(i + 4, mc) Cells(i + 1, mc) = "Common" Cells(i + 2, mc) = "Curve:" Cells(i + 3, mc) = "Measr.Depth" End If Next i Columns(mc).ColumnWidth = 5 '7.14 'Columns("a:b").AutoFit Application.ScreenUpdating = True End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "James" wrote in message ... I have a file that I thought was in a great format for my application but it turns out its not. Here is an example of what I have: 1 12.1 1 12.6 1 12.9 2 8.4 2 8.7 3 30.1 3 30.9 3 31.0 Column A is sample number and when it changes to a new sample number I need something that inserts 4 rows with the 1st row = UWI: 2nd row = Common: 3rd row = Curve: 4th row = Measr. Depth Below is what it should like but it needs to happen everytime it switches to a new sample #. UWI: Common: Curve: Measr. Depth 1 12.1 1 12.6 1 12.9 It would be great if it could also put the sample number in the UWI field so in the above example it would look like not necessary but would be nice. UWI: 1 Common: Curve: Measr. Depth 1 12.1 1 12.6 1 12.9 as always thanks for your help. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Adding rows
Hard to tell from your example. If desired, send your wb to my address with clear explanations, snippets of these messages on an inserted sheet, and before/after example(s). -- Don Guillett Microsoft MVP Excel SalesAid Software "James" wrote in message ... Don, This is great!!! I have something like 20,000 rows and this has just saved me a lot of heartbreak. Can you help with an additional step that I think will help my format. Below is what the code did for me and its great. The cells below "Measr. Depth", need to be deleted and the data that is in column B and C need to move to column A and B, is this possible or is it asking to much from a code. UWI. 25025210790000 Common. Curve. PHIE Measr. Depth 25025210790000 8900.0 0.056111765 25025210790000 8900.2 0.059111765 25025210790000 8900.4 0.061111765 UWI. 25025210930000 Common. Curve. PHIE Measr. Depth 25025210930000 9350.0 0.05211 25025210930000 9350.2 0.05211 25025210930000 9350.4 0.05261 Thanks again "Don Guillett" wrote: Sub createtable() Application.ScreenUpdating = False mc = 1 '"a" For i = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1 If Cells(i - 1, mc) < Cells(i, mc) Then Rows(i).Resize(4).Insert 'MsgBox i Cells(i, mc) = "UWI:" & Cells(i + 4, mc) Cells(i + 1, mc) = "Common" Cells(i + 2, mc) = "Curve:" Cells(i + 3, mc) = "Measr.Depth" End If Next i Columns(mc).ColumnWidth = 5 '7.14 'Columns("a:b").AutoFit Application.ScreenUpdating = True End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "James" wrote in message ... I have a file that I thought was in a great format for my application but it turns out its not. Here is an example of what I have: 1 12.1 1 12.6 1 12.9 2 8.4 2 8.7 3 30.1 3 30.9 3 31.0 Column A is sample number and when it changes to a new sample number I need something that inserts 4 rows with the 1st row = UWI: 2nd row = Common: 3rd row = Curve: 4th row = Measr. Depth Below is what it should like but it needs to happen everytime it switches to a new sample #. UWI: Common: Curve: Measr. Depth 1 12.1 1 12.6 1 12.9 It would be great if it could also put the sample number in the UWI field so in the above example it would look like not necessary but would be nice. UWI: 1 Common: Curve: Measr. Depth 1 12.1 1 12.6 1 12.9 as always thanks for your help. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Adding rows
Don,
Can you modify the original code to just put the UWI, Common, Curve, Measr. Depth in Column B instead of A that would fix my problem. Thanks "James" wrote: Don, This is great!!! I have something like 20,000 rows and this has just saved me a lot of heartbreak. Can you help with an additional step that I think will help my format. Below is what the code did for me and its great. The cells below "Measr. Depth", need to be deleted and the data that is in column B and C need to move to column A and B, is this possible or is it asking to much from a code. UWI. 25025210790000 Common. Curve. PHIE Measr. Depth 25025210790000 8900.0 0.056111765 25025210790000 8900.2 0.059111765 25025210790000 8900.4 0.061111765 UWI. 25025210930000 Common. Curve. PHIE Measr. Depth 25025210930000 9350.0 0.05211 25025210930000 9350.2 0.05211 25025210930000 9350.4 0.05261 Thanks again "Don Guillett" wrote: Sub createtable() Application.ScreenUpdating = False mc = 1 '"a" For i = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1 If Cells(i - 1, mc) < Cells(i, mc) Then Rows(i).Resize(4).Insert 'MsgBox i Cells(i, mc) = "UWI:" & Cells(i + 4, mc) Cells(i + 1, mc) = "Common" Cells(i + 2, mc) = "Curve:" Cells(i + 3, mc) = "Measr.Depth" End If Next i Columns(mc).ColumnWidth = 5 '7.14 'Columns("a:b").AutoFit Application.ScreenUpdating = True End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "James" wrote in message ... I have a file that I thought was in a great format for my application but it turns out its not. Here is an example of what I have: 1 12.1 1 12.6 1 12.9 2 8.4 2 8.7 3 30.1 3 30.9 3 31.0 Column A is sample number and when it changes to a new sample number I need something that inserts 4 rows with the 1st row = UWI: 2nd row = Common: 3rd row = Curve: 4th row = Measr. Depth Below is what it should like but it needs to happen everytime it switches to a new sample #. UWI: Common: Curve: Measr. Depth 1 12.1 1 12.6 1 12.9 It would be great if it could also put the sample number in the UWI field so in the above example it would look like not necessary but would be nice. UWI: 1 Common: Curve: Measr. Depth 1 12.1 1 12.6 1 12.9 as always thanks for your help. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Adding rows
Sub createtable() 'SalesAidSoftware
Application.ScreenUpdating = False mc = 1 '"a" For i = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1 If Cells(i - 1, mc) < Cells(i, mc) Then Rows(i).Resize(4).Insert Cells(i, mc + 1) = "UWI: " & Cells(i + 4, mc) Cells(i + 1, mc + 1) = "Common" Cells(i + 2, mc + 1) = "Curve: PHIE" Cells(i + 3, mc + 1) = "Measr.Depth" End If Next i Columns(mc + 1).Resize(, 2).ColumnWidth = 10.3 Columns(mc).Delete Application.ScreenUpdating = True End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "James" wrote in message ... Don, Can you modify the original code to just put the UWI, Common, Curve, Measr. Depth in Column B instead of A that would fix my problem. Thanks "James" wrote: Don, This is great!!! I have something like 20,000 rows and this has just saved me a lot of heartbreak. Can you help with an additional step that I think will help my format. Below is what the code did for me and its great. The cells below "Measr. Depth", need to be deleted and the data that is in column B and C need to move to column A and B, is this possible or is it asking to much from a code. UWI. 25025210790000 Common. Curve. PHIE Measr. Depth 25025210790000 8900.0 0.056111765 25025210790000 8900.2 0.059111765 25025210790000 8900.4 0.061111765 UWI. 25025210930000 Common. Curve. PHIE Measr. Depth 25025210930000 9350.0 0.05211 25025210930000 9350.2 0.05211 25025210930000 9350.4 0.05261 Thanks again "Don Guillett" wrote: Sub createtable() Application.ScreenUpdating = False mc = 1 '"a" For i = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1 If Cells(i - 1, mc) < Cells(i, mc) Then Rows(i).Resize(4).Insert 'MsgBox i Cells(i, mc) = "UWI:" & Cells(i + 4, mc) Cells(i + 1, mc) = "Common" Cells(i + 2, mc) = "Curve:" Cells(i + 3, mc) = "Measr.Depth" End If Next i Columns(mc).ColumnWidth = 5 '7.14 'Columns("a:b").AutoFit Application.ScreenUpdating = True End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "James" wrote in message ... I have a file that I thought was in a great format for my application but it turns out its not. Here is an example of what I have: 1 12.1 1 12.6 1 12.9 2 8.4 2 8.7 3 30.1 3 30.9 3 31.0 Column A is sample number and when it changes to a new sample number I need something that inserts 4 rows with the 1st row = UWI: 2nd row = Common: 3rd row = Curve: 4th row = Measr. Depth Below is what it should like but it needs to happen everytime it switches to a new sample #. UWI: Common: Curve: Measr. Depth 1 12.1 1 12.6 1 12.9 It would be great if it could also put the sample number in the UWI field so in the above example it would look like not necessary but would be nice. UWI: 1 Common: Curve: Measr. Depth 1 12.1 1 12.6 1 12.9 as always thanks for your help. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Adding rows based on no of rows specified from a given position | Excel Worksheet Functions | |||
| Adding like rows | Excel Discussion (Misc queries) | |||
| Adding five new rows every 40 rows in a spreadsheet? | Excel Discussion (Misc queries) | |||
| Adding Rows offsets to working rows across two worksheets | Setting up and Configuration of Excel | |||
| adding rows | Excel Discussion (Misc queries) | |||
Linear Mode
