Master Work
asked on
I need to sort and sum data in auto mode and plot it accordingly
I need to sort and sum data in auto mode and plot it accordingly. The original data and the different steps I want to apply are explained in the attached sheet.
Regards,
Dallag
Proj1.xlsx
Regards,
Dallag
Proj1.xlsx
and then start to cum the valuesDo you mean "sum the values"?
^ they're doing a running total. I think he's using "cum" as short for cumulative.
@Dallag,
I omitted the extra sort category in my example, but the cumulative formulas will still work. Instead of separate tables (which demonstrate the transition), it would be better to just add the cumulative columns to the original table. That would ensure that the sorted order is taken into account (and it greatly simplifies the formulas).
See the revised example workbook.
-Glenn
I omitted the extra sort category in my example, but the cumulative formulas will still work. Instead of separate tables (which demonstrate the transition), it would be better to just add the cumulative columns to the original table. That would ensure that the sorted order is taken into account (and it greatly simplifies the formulas).
See the revised example workbook.
-Glenn
ASKER
My input data to the excel sheet is not sorted by MRC_Group or GOR. It is like the first table and it could have less or more data than the first table. What I need is first to sort the table in auto mode based on MRC_Group then GOR. Then, sum the values of GOR and Oil starting from the smallest GOR.
I need also to display the well name as a label on each curve.
Thank you so much for your help
Regards,
Dallag
I need also to display the well name as a label on each curve.
Thank you so much for your help
Regards,
Dallag
ASKER
sum = running sum
CUM = running sum
CUM = running sum
@Dallag:
Are you requesting an automated (i.e., VBA/macro) solution?
The formulas I provided in the updated workbook are not dependent on the order of the original data as it comes in, but they will only provide meaningful results once the data is re-sorted by MRC and GOR.
-Glenn
Are you requesting an automated (i.e., VBA/macro) solution?
The formulas I provided in the updated workbook are not dependent on the order of the original data as it comes in, but they will only provide meaningful results once the data is re-sorted by MRC and GOR.
-Glenn
ASKER
Yes please, I need to an automated VBA/macro solution.
Another program is feeding the excel sheet and I need to automate the sorting and the cumulative calculations.
Regards,
Dallag
Another program is feeding the excel sheet and I need to automate the sorting and the cumulative calculations.
Regards,
Dallag
ASKER
Any help in this please and why my ticket is Neglected??
Dallag:
The "Neglected" message is an automated process that checks open questions that have had no solution noted for three or more days.
Here is a VBA solution that automates the process of sorting, inserting running (cumulative) totals and charting the data by MRC_Group.
So long as your actual data is contiguous (i.e,, no blank rows) and is exactly four columns as shown in your examples, this should generate a scatter chart plotting all GOR values by Oil values (cumulative), one series per MRC group.
An example file is attached.
Regards,
-Glenn
The "Neglected" message is an automated process that checks open questions that have had no solution noted for three or more days.
Here is a VBA solution that automates the process of sorting, inserting running (cumulative) totals and charting the data by MRC_Group.
Option Explicit
Sub Prepare_Well_Data()
Dim rngInput, rngMRC As Range
Dim cl As Object
Dim strChartName As String
Dim lngInputRows As Long
Dim intSeries As Integer
Dim r1, r2 As Long
Sheets("Sheet1").Select
Application.ScreenUpdating = False
lngInputRows = Range("A1").End(xlDown).Row
Set rngInput = Range("A1:D" & lngInputRows)
Range("A1").Value = "Name"
Range("B1").Value = "GOR"
Range("C1").Value = "Oil"
'Sort well data
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("D2:D" & lngInputRows) _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("B2:B" & lngInputRows) _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange rngInput
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
'add cumulative formulas
Range("E1").Value = "GOR Cumulative"
Range("F1").Value = "Oil Cumulative"
Range("E2").Select
ActiveCell.Formula = "=B2+IF($D2<>$D1,0,E1)"
Range("F2").Formula = "=C2+IF($D2<>$D1,0,F1)"
Range("E2:F2").Copy
Range("E3:F" & lngInputRows).PasteSpecial
Application.CutCopyMode = False
Range("E2:F" & lngInputRows).NumberFormat = "0.00"
Columns("A:F").AutoFit
'Chart data by MRC_Group
Set rngMRC = Range("D2:D" & lngInputRows + 1)
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlXYScatterSmooth
strChartName = Mid(ActiveChart.Name, InStr(1, ActiveChart.Name, " ", vbTextCompare) + 1, 40)
r1 = 2: r2 = 2: intSeries = 1
For Each cl In rngMRC
If cl.Value <> cl.Offset(1, 0).Value Then 'create new series
ActiveSheet.ChartObjects(strChartName).Activate
ActiveChart.SeriesCollection(intSeries).Select
ActiveChart.SeriesCollection(intSeries).Name = "=Sheet1!$D$" & r1
ActiveChart.SeriesCollection(intSeries).XValues = "=Sheet1!$E$" & r1 & ":$E$" & r2
ActiveChart.SeriesCollection(intSeries).Values = "=Sheet1!$F$" & r1 & ":$F$" & r2
r1 = cl.Row + 1
intSeries = intSeries + 1
End If
r2 = r2 + 1
Next cl
ActiveChart.SetElement (msoElementLegendTop)
ActiveChart.ChartArea.Select
ActiveSheet.Shapes(strChartName).Top = 20
ActiveSheet.Shapes(strChartName).Height = 360
ActiveSheet.Shapes(strChartName).Width = 576
Application.ScreenUpdating = True
End Sub
So long as your actual data is contiguous (i.e,, no blank rows) and is exactly four columns as shown in your examples, this should generate a scatter chart plotting all GOR values by Oil values (cumulative), one series per MRC group.
An example file is attached.
Regards,
-Glenn
Hi,
Did you have any questions about my solution (40348297) above? If it works, please properly close this question by clicking the "Accept this solution" link above that post. That will allow future EE users to reference this solution.
If you still have any issues, let me know.
Thanks,
-Glenn
Did you have any questions about my solution (40348297) above? If it works, please properly close this question by clicking the "Accept this solution" link above that post. That will allow future EE users to reference this solution.
If you still have any issues, let me know.
Thanks,
-Glenn
ASKER
I tested the script and it is working fine. Can we have the results in another sheet and chart once the orginal data is copied. Another thing I need to name of the well to be diplay on top of each point like a point label.
Regards,
Mohammed Dallag
Regards,
Mohammed Dallag
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thank you for your excellent support
ASKER
How can I have the results after puting the data in sheet1 without runining the macro?
Regards,
Dallag
Regards,
Dallag
ASKER
How can I have the results after puting the data in sheet1 without runining the macro?
Regards,
Dallag
Regards,
Dallag
(Side Note: Your middle table on sheet "S1" had the data for SHYB-446 on row 2, instead of SHYB-466.)
In cell R2, copied down:
=VLOOKUP($Q2,$A$2:$D$11,2,
In cell S2, copied down:
=VLOOKUP($Q2,$A$2:$D$11,3,
And, for good measure, in cell T2, copied down:
=VLOOKUP($Q2,$A$2:$D$11,4,
(this brings over the MRC groups)
So long as the data is sorted first by MRC_Group, the formulas should calculate running totals.
Before continuing with any automation, please review the attached workbook to ensure that the workflow and data are correct.
Regards,
-Glenn