Separate data by month

Question

I have an employer list indicating the reinforcement shifts per dd/mm/yyyy in an Excel 2003 workbook.

With the next macro I get in the same document, all the GP per person multiplied by 4.83 indicating the result in a new column.

Option Explicit

Sub Resumen()
'------------------
'by Cacho Rodríguez
'------------------
Dim C As Range, Mat, Q&, i&, R&

On Error Resume Next
Set C = Application.InputBox("Selecciona la celda superior izquierda (CODIGO NÓMINA)" & vbLf & _
  "de tu rango de datos." & vbLf & vbLf & "(por ejemplo: Full1!$A$1)", Type:=8)
If C Is Nothing Then Exit Sub
On Error GoTo 0

Application.ScreenUpdating = False
With C.Worksheet
    Mat = .Range(C, .Cells(.Rows.Count, 1 + C.Column).End(xlUp).Offset(, 1))
End With
Q = UBound(Mat)
R = 1
Mat(R, 1) = Mat(1, 1)
Mat(R, 2) = Mat(1, 2)
Mat(R, 3) = "GP"

For i = 2 To Q
    Select Case True
    Case Mat(i, 1) = ""
        Mat(R, 3) = 1 + Mat(R, 3)

    Case IsNumeric(Mat(i, 1))
        R = 1 + R
        Mat(R, 1) = 0 + Mat(i, 1)
        Mat(R, 2) = Mat(i, 2)
        Mat(R, 3) = 0
   End Select
Next

C.Worksheet.[g1].CurrentRegion.Delete xlUp
With C.Worksheet.[g1].Resize(R, 3)
    Application.Goto .Cells(1).Offset(, -3), True
    .Value = Mat
    .Columns(4) = "=4.83 * " & .Cells(1, 3).Address(0, 0)
    .Cells(1, 4) = "Total"
    .Resize(, 4).Columns.AutoFit
End With
Application.ScreenUpdating = True
End Sub

I need too all GF in a new column and in the "Total column", the result of GP+GF*4.83.

But I need the GP and GF separate per month, and the total per month per employer.

For example something like the next picture:

Answer 1

It took a bit for me to figure out what you want to do. If I understand properly: your 3rd image is a Summary of the data in the 1st image, and you want it to also include the data from your 2nd image.

If this is going to be an ongoing report then your first step should be organizing the data better, which will then make this and anything else you ever want to do with this data in Excel a lot easier for you and others.

If your data were organized like this:

...then with just a few clicks, you can have you data displayed like this automatically:

...and any time you add or change data, it take 1 click to update this table. It only took a few minutes to create this pivot table (now that the data is organized properly).

One in place, the pivot table can be changed with only a few clicks to instantly report on the data in different ways.

Same goes for Charts (which took a couple minutes to create, and will automatically update when the data changes) and various other Excel features:

You're doing things the "hard way" by using VBA to create your reports -- but it's very common from users who aren't aware of the functionality already built-in to Excel. But as I said, the first step in organizing your data in a more logical fashion (basically, "one record per row" with no sub-headings in between rows, like the Nom on your sample data.)

If you'd like to play around with the workbook I used for the examples, you can download it from Jumpshare here. (It probably won't display properly on the JumpShare website (because of the charts, etc) but click the Download button to download the [macro-free] .XLSX file.

---

More Information:

• Microsoft : Guidelines for organizing and formatting data on a worksheet

• Hubspot : How to Create a Pivot Table in Excel: A Step-by-Step Tutorial (With Video)

• Office.com : Create a PivotTable to analyze worksheet data

• GCFLearnFree: Introduction to Pivot Tables (with practice workbook)