|
|
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.
|
|
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
dguillett1[ at ]austin.rr.com
"James" <James[ at ]discussions.microsoft.com> wrote in message
news:EF68D2D7-84F3-41E1-BC1C-FCDE1B76D77E[ at ]microsoft.com...
[Quoted Text] >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,
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:
[Quoted Text] > 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
> dguillett1[ at ]austin.rr.com
> "James" <James[ at ]discussions.microsoft.com> wrote in message
> news:EF68D2D7-84F3-41E1-BC1C-FCDE1B76D77E[ at ]microsoft.com...
> >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.
> >
>
>
|
|
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
dguillett1[ at ]austin.rr.com
"James" <James[ at ]discussions.microsoft.com> wrote in message
news:DC815A08-91EE-4B6B-93D6-216353A94AD6[ at ]microsoft.com...
[Quoted Text] > 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
>> dguillett1[ at ]austin.rr.com
>> "James" <James[ at ]discussions.microsoft.com> wrote in message
>> news:EF68D2D7-84F3-41E1-BC1C-FCDE1B76D77E[ at ]microsoft.com...
>> >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,
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:
[Quoted Text] > 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
> > dguillett1[ at ]austin.rr.com
> > "James" <James[ at ]discussions.microsoft.com> wrote in message
> > news:EF68D2D7-84F3-41E1-BC1C-FCDE1B76D77E[ at ]microsoft.com...
> > >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.
> > >
> >
> >
|
|
|