rearranging data
 

Is there an Excel formula or VB procedure to take the raw data below and
produce the output, also shown below?

raw data:
WELL ZONE COUNTY STATE DEPTH POROSITY
1 A HARRIS TX 3000 0.14
1 C HARRIS TX 3500 0.33
1 D HARRIS TX 4500 0.21
2 B PECOS TX 3300 0.11
2 D PECOS TX 4700 0.34
3 A GREENE MO 4000 0.22
3 E GREENE MO 5000 0.19
3 F GREENE MO 6000 0.17

output:
WELL 1 2 3
COUNTY HARRIS PECOS GREENE
STATE TX TX MO
ZONE A
DEPTH 3000 4000
POROSITY 0.14 0.22
ZONE B
DEPTH 3300
POROSITY 0.11
ZONE C
DEPTH 3500
POROSITY 0.33
ZONE D
DEPTH 4500 4700
POROSITY 0.21 0.34
ZONE E
DEPTH 5000
POROSITY 0.19
ZONE F
DEPTH 6000
POROSITY 0.17


Mark Roach
Vice President Technical Resources
The Oil & Gas Asset Clearinghouse
Houston, Texas

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

rearranging data
 

Hi Mark
have you tried using a pivot table for this. Should get you quite close to
your desired output

"Mark Roach" wrote:

Is there an Excel formula or VB procedure to take the raw data below and
produce the output, also shown below?

raw data:
WELL ZONE COUNTY STATE DEPTH POROSITY
1 A HARRIS TX 3000 0.14
1 C HARRIS TX 3500 0.33
1 D HARRIS TX 4500 0.21
2 B PECOS TX 3300 0.11
2 D PECOS TX 4700 0.34
3 A GREENE MO 4000 0.22
3 E GREENE MO 5000 0.19
3 F GREENE MO 6000 0.17

output:
WELL 1 2 3
COUNTY HARRIS PECOS GREENE
STATE TX TX MO
ZONE A
DEPTH 3000 4000
POROSITY 0.14 0.22
ZONE B
DEPTH 3300
POROSITY 0.11
ZONE C
DEPTH 3500
POROSITY 0.33
ZONE D
DEPTH 4500 4700
POROSITY 0.21 0.34
ZONE E
DEPTH 5000
POROSITY 0.19
ZONE F
DEPTH 6000
POROSITY 0.17


Mark Roach
Vice President Technical Resources
The Oil & Gas Asset Clearinghouse
Houston, Texas

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

rearranging data
 

Morning Mark,
Try recording a macro where you select area, select copy, click desired
first cell position, then paste special where you use option transpose. Stop
your recording and view the code generated.

HTH, Dean.

"Mark Roach" wrote:

Is there an Excel formula or VB procedure to take the raw data below and
produce the output, also shown below?

raw data:
WELL ZONE COUNTY STATE DEPTH POROSITY
1 A HARRIS TX 3000 0.14
1 C HARRIS TX 3500 0.33
1 D HARRIS TX 4500 0.21
2 B PECOS TX 3300 0.11
2 D PECOS TX 4700 0.34
3 A GREENE MO 4000 0.22
3 E GREENE MO 5000 0.19
3 F GREENE MO 6000 0.17

output:
WELL 1 2 3
COUNTY HARRIS PECOS GREENE
STATE TX TX MO
ZONE A
DEPTH 3000 4000
POROSITY 0.14 0.22
ZONE B
DEPTH 3300
POROSITY 0.11
ZONE C
DEPTH 3500
POROSITY 0.33
ZONE D
DEPTH 4500 4700
POROSITY 0.21 0.34
ZONE E
DEPTH 5000
POROSITY 0.19
ZONE F
DEPTH 6000
POROSITY 0.17


Mark Roach
Vice President Technical Resources
The Oil & Gas Asset Clearinghouse
Houston, Texas

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

rearranging data
 

Is there a way to move text around with a pivot table? e.g., if the
data element is County or State, in addition to something like depth or
porosity?

Mark Roach
Vice President Technical Resources
The Oil & Gas Asset Clearinghouse
Houston, Texas

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

rearranging data
 

Mark -

Doesn't sound like you gave it much of a try. Select your data, and generate a pivot
table (Data menu).

Drag the Well, County, and State fields to the columns area. If they aren't in the
order you want, drag the buttons around within the columns area to see how that
changes the order.

Drag the Zone field to the Rows area.

Drag the Depth and Porosity fields to the Data area. If they aren't in the right
order, drag them around.

The nice thing about a pivot table is that you can very easily move the different
field buttons, within an area or to another area. To get rid of subtotals, double
click on the buttons, and choose the None option for Subtotals.

Here's a 30-second pivot table of your data:

WELL COUNTY STATE
1 2 3
HARRIS PECOS GREENE
ZONE Data TX TX MO
A DEPTH 3000 4000
POROS 0.14 0.22
B DEPTH 3300
POROS 0.11
C DEPTH 3500
POROS 0.33
D DEPTH 4500 4700
POROS 0.21 0.34
E DEPTH 5000
POROS 0.19
F DEPTH 6000
POROS 0.17


- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______


Mark Roach wrote:

Is there a way to move text around with a pivot table? e.g., if the
data element is County or State, in addition to something like depth or
porosity?

Mark Roach
Vice President Technical Resources
The Oil & Gas Asset Clearinghouse
Houston, Texas

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

rearranging data
 

Mark Roach
Vice President Technical Resources
The Oil & Gas Asset Clearinghouse
Houston, Texas

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

rearranging data
 

Thanks Jon.

I don't think you can use text in the data region because it doesn't
sum. Of course I'd be interested if there is a way to pull text into
the data region of a pivot table.

Yes, I know it can be worked fairly closely with a pivot table.
However, not everyone grasps that concept or procedure required to
produce pt's. I'm looking for a solution with formulae - passive to
users.



Mark Roach
Vice President Technical Resources
The Oil & Gas Asset Clearinghouse
Houston, Texas

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

rearranging data
 

Hi Mark -

If you put a text field into the data area, Excel will provide a count of each text
value within the field. Your requested output did not require this, though.

Could your solution build the PT using VBA? Once I got over the initial
difficulties, I've found this to be a very powerful trick.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Mark Roach wrote:

Thanks Jon.

I don't think you can use text in the data region because it doesn't
sum. Of course I'd be interested if there is a way to pull text into
the data region of a pivot table.

Yes, I know it can be worked fairly closely with a pivot table.
However, not everyone grasps that concept or procedure required to
produce pt's. I'm looking for a solution with formulae - passive to
users.



Mark Roach
Vice President Technical Resources
The Oil & Gas Asset Clearinghouse
Houston, Texas

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

rearranging data
 

Nice idea, Jon. How would the following be modified to show the state
and county rather than count them?

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 12/19/2004 by Mark Roach
'

'
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:=
_
"Sheet1!R3C1:R11C7").CreatePivotTable TableDestination:="",
TableName:= _
"PivotTable2", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3,
1)
ActiveSheet.Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable2")
.ColumnGrand = False
.RowGrand = False
End With
ActiveSheet.PivotTables("PivotTable2").AddFields
RowFields:=Array("ZONE", _
"Data"), ColumnFields:="WELL"
With ActiveSheet.PivotTables("PivotTable2").PivotFields ("COUNTY")
.Orientation = xlDataField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields ("STATE")
.Orientation = xlDataField
.Position = 2
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields ("DEPTH")
.Orientation = xlDataField
.Position = 3
End With

ActiveSheet.PivotTables("PivotTable2").PivotFields ("POROSITY").Orientati
on = _
xlDataField
End Sub

Mark Roach
Vice President Technical Resources
The Oil & Gas Asset Clearinghouse
Houston, Texas

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

rearranging data
 

Mark -

In my example of a few posts ago, I added the county and state as column fields, not
data fields.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Mark Roach wrote:

Nice idea, Jon. How would the following be modified to show the state
and county rather than count them?

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 12/19/2004 by Mark Roach
'

'
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:=
_
"Sheet1!R3C1:R11C7").CreatePivotTable TableDestination:="",
TableName:= _
"PivotTable2", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3,
1)
ActiveSheet.Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable2")
.ColumnGrand = False
.RowGrand = False
End With
ActiveSheet.PivotTables("PivotTable2").AddFields
RowFields:=Array("ZONE", _
"Data"), ColumnFields:="WELL"
With ActiveSheet.PivotTables("PivotTable2").PivotFields ("COUNTY")
.Orientation = xlDataField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields ("STATE")
.Orientation = xlDataField
.Position = 2
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields ("DEPTH")
.Orientation = xlDataField
.Position = 3
End With

ActiveSheet.PivotTables("PivotTable2").PivotFields ("POROSITY").Orientati
on = _
xlDataField
End Sub

Mark Roach
Vice President Technical Resources
The Oil & Gas Asset Clearinghouse
Houston, Texas

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!