Solved

I need to sort and sum data in auto mode and plot it accordingly

Posted on 2014-09-24
19
92 Views
Last Modified: 2014-12-04
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
0
Comment
Question by:dallagmm
  • 10
  • 7
19 Comments
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40343283
Although I don't understand the purpose of a running total, I think I can offer formulas to produce this directly from the original data set.

(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,FALSE)+IF($T2<>$T1,0,R1)
In cell S2, copied down:
=VLOOKUP($Q2,$A$2:$D$11,3,FALSE)+IF($T2<>$T1,0,S1)
And, for good measure, in cell T2, copied down:
=VLOOKUP($Q2,$A$2:$D$11,4,FALSE)
(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
EE-Proj1.xlsx
0
 

Author Comment

by:dallagmm
ID: 40343640
First we need to sort the GOR for each MRC_Group and then start to cum the values of GOR for each MRC_Group.

So we should start with
SHYB-463      655.59      7391.68      0-5000
and then cum with the second highest GOR
SHYB-466      851.67      6107.06      0-5000
So for these two we should have:
SHYB-463      655.59      7391.68              0-5000
SHYB-466      1507.26      13498.74      0-5000

I fixed the well sorting in table 3 and well 466
Proj2.xlsx
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40343707
and then start to cum the values
Do you mean "sum the values"?
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40343925
^  they're doing a running total.  I think he's using "cum" as short for cumulative.
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40343959
@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
EE-Proj1.xlsx
0
 

Author Comment

by:dallagmm
ID: 40343989
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
0
 

Author Comment

by:dallagmm
ID: 40343994
sum = running sum
CUM = running sum
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40344212
@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
0
 

Author Comment

by:dallagmm
ID: 40344675
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
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:dallagmm
ID: 40348208
Any help in this please and why my ticket is Neglected??
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40348297
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.
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

Open in new window


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
EE-Proj1.xlsm
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40382469
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
0
 

Author Comment

by:dallagmm
ID: 40383974
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
0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 500 total points
ID: 40384604
Hi Mohammed,

1) Results in another sheet & chart:  I've modified the code to do this.  The original sheet (presumably "Sheet1") will remain unaffected.

2) Name of the well over each data point:  This wasn't explicitly requested in the original question, and will be difficult to do.  If you will properly close this question by accepting the solution provided here and then open a new question requesting that unique text labels replace data labels, I'll try to provide a solution.

Updated file attached.

Regards,
-Glenn
EE-Proj1.xlsm
0
 

Author Closing Comment

by:dallagmm
ID: 40396315
thank you for your excellent support
0
 

Author Comment

by:dallagmm
ID: 40398314
How can I have the results after puting the data in sheet1 without runining the macro?

Regards,

Dallag
0
 

Author Comment

by:dallagmm
ID: 40445389
How can I have the results after puting the data in sheet1 without runining the macro?

Regards,

Dallag
0
 

Author Comment

by:dallagmm
ID: 40475446
Dear Glenn,

I am getting error when I increase the groups in sheet1 (OFMsheet).
Could you please help in this.

Regards,

Dallag
Last-CumGOR.xls
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now