Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Rearranging data | Excel Discussion (Misc queries) | |||
Rearranging DATA | Excel Discussion (Misc queries) | |||
Rearranging data | Excel Discussion (Misc queries) | |||
rearranging data | Excel Worksheet Functions | |||
Rearranging Data Help... | Excel Discussion (Misc queries) |