Summarize Data from Dates to Months based on Criteria

Hi,

I have a project tracker file which I need to summarize (by count) on the next sheet based on the project status.

The tracker sheet has dates in the dd-mmm-yy format, whereas the summary sheet is month based (mmm-yy). Basically I would like to know the count of projects under different progress status in each month in the summary sheet.

I have attached a reference file.

Attachment 3796

HAPPY NEW YEAR

Hi Siyab, Happy New Year too :)
I personally prefer to avoid using Excel’s built in date things if possible, and that seems simple to do here, even if it may not result in the best most efficient coding.
Having said that it’s more normal and professional to handle the thing with Excel’s built in date things.
But I am not doing that in this example.

I am doing in this example the simplest and probably most inefficient worksheets interaction type coding

The macro below does a very simple text comparison for the specific sample you gave .
I have not made the macro dynamic: I have shown you in other of your Threads how to do that. ( Lr stuff etc. )

Code:

---

Sub SummarizeDatafromDatestoMonthsbasedonCriteria() '  https://excelfox.com/forum/showthread.php/2774-Summarize-Data-from-Dates-to-Months-based-on-Criteria
Rem 0 Worksheets info
Dim WsCT As Worksheet, WsS As Worksheet
 Set WsCT = ThisWorkbook.Worksheets("Case Tracker"): Set WsS = ThisWorkbook.Worksheets("Summary")
Rem 1 Simple worksheet solution loop in a loop in a loop
Dim rCT As Long, rS As Long, cS As Long
    For rS = 3 To 19 Step 1 ' _ === Loop rows in summary ============
        For rCT = 3 To 12 Step 1 ' Loop rows in Case Tracker data '------
        If Mid(WsCT.Range("B" & rCT & "").Text, 4) = WsS.Range("B" & rS & "").Text Then ' this is checking for a month matsch in the columns B of the worksheets
            For cS = 3 To 6 Step 1 ' loop columns in Summary '###############
                If WsCT.Range("D" & rCT & "").Value2 = WsS.Cells(2, cS).Value2 Then '      this checks for match in progress
                Let WsS.Cells(rS, cS).Value = WsS.Cells(rS, cS).Value + 1
                Else
                End If
            Next cS ' #######################################################
        Else
        End If
        Next rCT ' ------------------------------------------------------
    Next rS ' _ =====================================================
End Sub

---

results after running macro here https://excelfox.com/forum/showthrea...ll=1#post16305


I am not intending to give you the best efficient coding here. It’s intended to share some ideas.
( Of course, as ever, anyone else is welcome to add an alternative solution for you, even I might do another if I feel like it, for my own amusement later )




Alan

Hello DocAElstein,

Tried out your macro. It works up to a certain extent.

1. When new data is entered on the subsequent rows, the values do not refresh on the summary sheet.
2. I have a similar template for other tasks (same formatting, same criteria, etc.) but no results appear in the summary sheet.
3. In case a criteria does not have any tasks, can the value shows as "0" (Zero).

Thanks
Siyab

Hi Siyab,
_1

Quote:

---

Originally Posted by msiyab

When new data is entered on the subsequent rows, the values do not refresh on the summary sheet.

---

Of course they do not. Why should they? You never mentioned anything about wanting the thing to update automatically.
_2 and _3 :confused: I am not sure what it is you are wanting or asking.

---

Possibly two things you need to be considering:
_ Macros do not run by magic when you want them to. You should no that. It’s obvious.
If you want a macro to run when something is done to the worksheet then you need to be considering worksheet event macros as I have done for you before.
_ I already mentioned that I just gave you a macro to give you some ideas. I already mentioned that you need to consider things like making the last row, Lr, dynamic

---

Your summary in your sample file may be an Excel Table (List Object)? , I am not sure. There may be some efficient ways to deal with Excel tables. I don’t know. I have no experience with Excel tables.

---

Siyab,
I seriously don’t wish to be impolite or rude, but you have been involved with VBA and excel for 10 years now which is much longer than I have. You may be tackling some diverse subjects, some of which I have no knowledge of.

If you cannot or don’t want to learn , and just want someone to write coding for you then that is perfectly OK. That is your right.
But excelfox.com may not currently be the best forum for you, just now. There are not so many people helping here just now, and I am happy to help, and explain, but don’t have the time to just keep writing macros for you.
Maybe you could try excelforum.com or mrexcel.com. - There are a lot more people there with much more time on there hands and some may be happy to keep just writing macros for you. You will probably get a quicker response there, currently. There are a larger number of helpers there, with a larger range of experience and knowledge of Excel
than those currently at excelfox.com
Maybe later we will have some more people here including some just happy to write any coding for you.
Just now there is mostly only me here and I don’t have so much time for you, sorry.


Alan




P.S: Did you see this?
https://excelfox.com/forum/showthrea...ll=1#post15705