Adding rows

[James]

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.

 

[Don Guillett]

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

 

[James]

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]

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).

 

[James]

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

 

[Don Guillett]

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