• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA Macro - Several Groupings in column A

k3vsmith

k3vsmith

Member
I'm stuck on an issue. Im creating a VBA Macro. I have several columns (A - P). A is several WBS ID #'s. I have a list of tasks to do on a group of WBS #'s. We are just going to concentrate on one of the tasks. See below
For example
Column A will looks like this:
01.01.03
01.01.03.01
01.01.03.02
01.01.03.03
01.01.04
01.01.04.01
01.01.04.02
I want to sum the numbers in column P of 01.01.03... and put total on column P for Master (01.01.03). So Id be summing column P for 01.01.03.01, 01.01.03.02, 01.01.03.03 and putting the total on row 01.01.03. I'd want to do the same for 01.01.04. So sum 01.01.04.01 and 01.01.04.02 and put total on column P for 01.01.04.
Please help.
 
Uploading TestReport.xls. The first worksheet is what Report looks like. 2nd Worksheet is what I want report to look like with the changes explained. Let me know if this makes sense.
 
In the attached see in the What I want sheet from cell F74 is something close to what you want. What's difficult is that you want the total to be somewhere in the middle of a given WBS level with variously the total being at the bottom of the group, at the top, and in the middle. Currently, a line is added for each subtotal.

The table at F74 is a copy of part of the pivot table above, and the way I got that was first, in the Test Report sheet, to do a text to columns of column A and put the result starting in column R. Then I created the pivot table on the What I want sheet from this enlarged table on the Test Report sheet.

Once that F74 table is made all the other changes can be reversed and the pivot table removed. Of course the whole shebang can be automated.
 

Attachments

  • chandoo27952TestReport01.xls
    148 KB · Views: 3
Im not sure that this will give me what I want, but maybe it is.
So from the report. Users are going to mainly look at the WBS Element Indicator of W (filtered on W) and from there want to see totals. What you provided though may help in getting those totals on to that line. Possibly doing a copy total up to W line.
I dont know if there is an "easy" way to do this. Some of these reports could have upwards of 500 rows.
 
You're creating this report. Where/what is the source data? It'll probably be easier to take 1 step back in order to take several steps forwards.
 
The original is an export from an external application to excel. At that point I want to run a macro against it to adjust to the way we want to view the data.
 
…so it would be helpful to know something about that data.
How it gets into Excel - does the external app. export it, can the export process be tweaked, or does Excel query the external app.'s data?
 
So its a report button that you press that is not able to be customized (came builtin with application). You press the button and I assume it query s data it needs and shoots out the excel spreadsheet. Data is all correct and matches up but for meetings this report is to much information/data is not setup how we'd(mgmt) like to see it. I spoke with the vendor already and they told me to do basically what I'm doing and either manually adjust worksheet or create macro. The vendor doesn't yet allow for customized reports to that level. More of less they only allow adding/removing columns.
So Im stuck adjusting the given report.
 
Ok, its already been posted. See Test Report.xls in beginning of this thread. I just changed the activity names and WBS description names to "test" to protect company information. But that's a direct export for a small project. "What I want" worksheet is my altered output (manually done).
 
So the output produces these extra lines with W, C, and the like, randomly postioned among rows of the same level, in it?!!
Can it export to anything else apart from an Excel file.
 
Im not sure what you mean by extra lines? No extra lines are added. This is a correct output from applications perspective. This is how all is read in application.
The WBS element Indicator is a column where W is work pacakge, O is operator, C is control account and blanks are activities.
 
Row 16 of the sheet carries a W in column C. It is in the middle of WBS Level 01.300.03.02.01 ; what's the logic behind positioning it there? One would normally expect to see such lines at the start or end of a WBS level, not randomly (apparently) somewhere in the middle of a WBS Level. Worse; it IS at the start or end sometimes.

Again, can it export to anything else apart from an Excel file?
 
Thats only because its sorted on WBS ID. If you do a custom sort on WBS ID and then the WBS Element Indicator that would put the W at the top of each if thats what you think may help. We can easily have it do the custom sort beforehand if this helps get me to my end result. Does having the W be the first line get this to be an easier task? See TestReport2.xls where I have the sort configured for WBS ID and then WBS Element Indicator (only edited first worksheet).
Yes, excel is only thing I can export too.
 

Attachments

  • TestReport2.xls
    54 KB · Views: 2
I'm out of time for now.
In your file from msg#4 (not #16, I haven't looked at it yet) sort by column A then C, then run this macro to see if it's getting close.
Code: 
Sub blah()
Range("D2").RemoveSubtotal
Range("D2").Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(15, 16), Replace:=True, PageBreaks:=False, SummaryBelowData:=False
ActiveSheet.Outline.ShowLevels RowLevels:=2
Set stuff = Range("O3:P39").SpecialCells(xlCellTypeVisible)
For Each cll In stuff.Cells
  cll.Offset(1, 2).Value = cll.Value
Next cll
Range("D2").RemoveSubtotal
Set stuff = Range("Q3:R32").SpecialCells(xlCellTypeConstants, 1)  '.Select
For Each are In stuff.Areas
  are.Offset(, -2).Value = are.Value
  are.Offset(, -2).Interior.ColorIndex = 6
  are.ClearContents
Next are
End Sub
Of course it neeeds more generalising but I'm out of time.
 
This is great. Thank you! It does what I want. I'm having trouble following the syntax but I can play around with it and look up what I don't understand.
 
Can you assist me on my second issue which is much like the first that you figured out. (Not sure if Im to create a new thread? If so, I will...)
I want to copy EV Method and EV description up to the W line. The way that we have sorted this could be a simple copy of EV Method and Description of the row under W up to W row assuming that line is blank.

So on WBS ID 01.300.03.01.01 the line with the W should have a matching EV Method and Description of the Blank line. So EV Method on row W for Test3 Activity should read 5 and EV description should read Percent Complete.

The next WBS ID of 01.300.03.02.01 would have P for EV Method and Milestone... for EV description on row with W. And so on.
 
If you Autofilter your real data on WBS Element Indicator and show everything but blanks, are the EV Method and EV Description columns completely blank (like they are here on your small sample of data)?
 
k3vsmith

I don't know if your initial problem have solved or not.
Here's the code for that
Code: 
Sub test()
    Dim x, i As Long, mySum As Double
    With Sheets("test report").Cells(1).CurrentRegion
        x = Filter(.Parent.Evaluate("transpose(if((countif(" & .Columns(1).Address & "," & .Columns(1).Address & ")>1)*(" & _
            .Columns("m").Address & "="""")*(" & .Columns("n").Address & "=""""),row(1:" & .Rows.Count & "),char(2)))"), Chr(2), 0)
        For i = 0 To UBound(x)
            mySum = .Parent.Evaluate("sum(if((" & .Columns("a").Address & "=a" & x(i) & ")*(" & .Columns("m").Address & "<>"""")*(" & .Columns("n").Address & "<>"""")," & .Columns("o").Address & "))")
            .Cells(x(i), "o").Value = mySum
            mySum = Evaluate("sum(if((" & .Columns("a").Address & "=a" & x(i) & ")*(" & .Columns("m").Address & "<>"""")*(" & .Columns("n").Address & "<>"""")," & .Columns("p").Address & "))")
            .Cells(x(i), "p").Value = mySum
            .Cells(x(i), "o").Resize(, 2).Font.Bold = True
        Next
    End With
End Sub
 
re msg#20, rough and ready, needs generalising:
Code: 
Sub blah2()
Range("C2").Select
Selection.AutoFilter
ActiveSheet.Range("$A$2").AutoFilter Field:=3, Criteria1:="<>"
Set stuff = Range("G3:H30").SpecialCells(xlCellTypeVisible)
Set stuff = stuff.SpecialCells(xlCellTypeBlanks)
For j = stuff.Areas.Count To 1 Step -1
  For i = stuff.Areas(j).Cells.Count To 1 Step -1
  ' stuff.Areas(j).Cells(i).Select
    stuff.Areas(j).Cells(i).Value = stuff.Areas(j).Cells(i).Offset(1).Value
  Next i
Next j
End Sub
 
You must log in or register to reply here.
Back
Top