SUMIF, DSUM, WTF (Excel anyone?)
Thread Starter
I hate the Mets
Joined: Jun 2002
Posts: 4,994
Likes: 0
From: Philly Burbs
SUMIF, DSUM, WTF (Excel anyone?)
If you need to do more than a pivot table in excel you have lost me...So when a colleague asked me to do this, I said "I'll see what I can do".
If one of you guys knows how to help, I would appreciate it...
The screen shot below is a completely dynamic table - There are many diferent values that can be added to it, changed, etc - so any solution cannot be tied to static data.
I need to create a new tab, that takes values out of column E and summarizes them so next to each K# or L# the proper number is summed up...
So in this case L6 = 222 based on column C and 255 based on column D
The tricky part is that it is completely dynamic and Columns A and B are always changing....
Time to break open the Excel help book....
If one of you guys knows how to help, I would appreciate it...
The screen shot below is a completely dynamic table - There are many diferent values that can be added to it, changed, etc - so any solution cannot be tied to static data.
I need to create a new tab, that takes values out of column E and summarizes them so next to each K# or L# the proper number is summed up...
So in this case L6 = 222 based on column C and 255 based on column D
The tricky part is that it is completely dynamic and Columns A and B are always changing....
Time to break open the Excel help book....
Safety Car
Joined: Jul 2007
Posts: 4,845
Likes: 145
Are you saying you can't reference C134 and D134 in your screenshot example because when you change the dropdowns in A6 and B6 the reference may not point to the summary value? i.e. it may be C120 and D120?
In that case, you may have to resort to using VBA to run down your A column looking for a known text value or formatting key to identify it as a summary row. I've seen similar cases where people want to format summary rows (http://excel.tips.net/Pages/T002984_...otal_Rows.html) or if you want to idenfiy them for later processing (http://excelusergroup.org/forums/p/1314/4065.aspx).
In that case, you may have to resort to using VBA to run down your A column looking for a known text value or formatting key to identify it as a summary row. I've seen similar cases where people want to format summary rows (http://excel.tips.net/Pages/T002984_...otal_Rows.html) or if you want to idenfiy them for later processing (http://excelusergroup.org/forums/p/1314/4065.aspx).
Thread Starter
I hate the Mets
Joined: Jun 2002
Posts: 4,994
Likes: 0
From: Philly Burbs
Sorry for being confusing...When I say "dynamic" , I mean to say that Column A lists different phases. These phases may change. Column B is a resource assigned to that Phase, these resources may change.
And when I say that the values may change, there may be different resources assigned, there may be more resources assigned or perhaps less resources assigned.
And when I say that the values may change, there may be different resources assigned, there may be more resources assigned or perhaps less resources assigned.
Suzuka Master
Joined: May 2006
Posts: 5,217
Likes: 150
From: off the grid
I understand static vs dynamic lol.. not sure where you're getting 222 or 255. I probably have no idea how to do this anyway - slow day at work I'd be willing to fuck around with it for a while.
Trending Topics
Thread Starter
I hate the Mets
Joined: Jun 2002
Posts: 4,994
Likes: 0
From: Philly Burbs
If you look at Column E there are different Values - I was adding up all the "Person Days w/o Cont" and "Person Days" where there was a K6.
Suzuka Master
Joined: May 2006
Posts: 5,217
Likes: 150
From: off the grid
Thread Starter
I hate the Mets
Joined: Jun 2002
Posts: 4,994
Likes: 0
From: Philly Burbs
Safety Car
Joined: Jul 2007
Posts: 4,845
Likes: 145
This should do what you want. It's not elegant, but it should be functional. You'll need to change the names of the worksheets referenced in the code, but assuming your columns are C, D, and E, the rest should work for you.
Paste this into the VBA editor (Alt-F11), then go under macros and run the new macro. One more thing. You need to add a reference to Microsoft.Scripting.Runtime. This is found under the Tools>References menu. Otherwise "Dictionary" will not be a known data type.
Paste this into the VBA editor (Alt-F11), then go under macros and run the new macro. One more thing. You need to add a reference to Microsoft.Scripting.Runtime. This is found under the Tools>References menu. Otherwise "Dictionary" will not be a known data type.
Code:
Public Sub GroupByCol()
Dim dict As New Dictionary
Dim colToGroupBy As String
Dim columnTotals As String
Dim colValues() As String
Dim sourceWorksheet As Worksheet
Dim targetWorksheet As Worksheet
Dim r As Long
Dim c As Long
' Change these to the real names of worksheets
Set sourceWorksheet = Me.Worksheets("Sheet1")
Set targetWorksheet = Me.Worksheets("Sheet2")
' Change this to the column that has the grouping keys
colToGroupBy = "E"
' Loop through all rows
For r = 1 To sourceWorksheet.Rows.Count
' If we hit a blank row, we're at the end of the sheet, so stop summing
If CStr(sourceWorksheet.Cells(r, "C")) = "" _
And CStr(sourceWorksheet.Cells(r, "D")) = "" _
And CStr(sourceWorksheet.Cells(r, "E")) = "" Then Exit For
' Either get the current totals for the group or create a new entry for totals
If dict.Exists(CStr(sourceWorksheet.Cells(r, colToGroupBy))) Then
columnTotals = dict(CStr(sourceWorksheet.Cells(r, colToGroupBy)))
colValues = Split(columnTotals, ",")
Else
columnTotals = "0,0"
colValues = Split(columnTotals, ",")
dict.Add CStr(sourceWorksheet.Cells(r, colToGroupBy)), columnTotals
End If
' Sum up the new values
colValues(0) = CLng(colValues(0)) + CLng(sourceWorksheet.Cells(r, "C"))
colValues(1) = CLng(colValues(1)) + CLng(sourceWorksheet.Cells(r, "D"))
' Store the update totals
dict(CStr(sourceWorksheet.Cells(r, colToGroupBy))) = Join(colValues, ",")
Next
' Switch to the sheet to hold the summaries
targetWorksheet.Activate
' Clear the sheet
targetWorksheet.Rows.Delete
' Loop through each group and write out the summaries
For r = 0 To dict.Count - 1
colValues = Split(dict.Items(r), ",")
With targetWorksheet
.Cells(r + 1, 1) = dict.Keys(r)
.Cells(r + 1, 2) = colValues(0)
.Cells(r + 1, 3) = colValues(1)
End With
Next
End Sub
Last edited by Anachostic; Jun 24, 2009 at 09:37 AM. Reason: Reference
Thread Starter
I hate the Mets
Joined: Jun 2002
Posts: 4,994
Likes: 0
From: Philly Burbs
This should do what you want. It's not elegant, but it should be functional. You'll need to change the names of the worksheets referenced in the code, but assuming your columns are C, D, and E, the rest should work for you.
Paste this into the VBA editor (Alt-F11), then go under macros and run the new macro. One more thing. You need to add a reference to Microsoft.Scripting.Runtime. This is found under the Tools>References menu. Otherwise "Dictionary" will not be a known data type.
Paste this into the VBA editor (Alt-F11), then go under macros and run the new macro. One more thing. You need to add a reference to Microsoft.Scripting.Runtime. This is found under the Tools>References menu. Otherwise "Dictionary" will not be a known data type.
Code:
Public Sub GroupByCol()
Dim dict As New Dictionary
Dim colToGroupBy As String
Dim columnTotals As String
Dim colValues() As String
Dim sourceWorksheet As Worksheet
Dim targetWorksheet As Worksheet
Dim r As Long
Dim c As Long
' Change these to the real names of worksheets
Set sourceWorksheet = Me.Worksheets("Sheet1")
Set targetWorksheet = Me.Worksheets("Sheet2")
' Change this to the column that has the grouping keys
colToGroupBy = "E"
' Loop through all rows
For r = 1 To sourceWorksheet.Rows.Count
' If we hit a blank row, we're at the end of the sheet, so stop summing
If CStr(sourceWorksheet.Cells(r, "C")) = "" _
And CStr(sourceWorksheet.Cells(r, "D")) = "" _
And CStr(sourceWorksheet.Cells(r, "E")) = "" Then Exit For
' Either get the current totals for the group or create a new entry for totals
If dict.Exists(CStr(sourceWorksheet.Cells(r, colToGroupBy))) Then
columnTotals = dict(CStr(sourceWorksheet.Cells(r, colToGroupBy)))
colValues = Split(columnTotals, ",")
Else
columnTotals = "0,0"
colValues = Split(columnTotals, ",")
dict.Add CStr(sourceWorksheet.Cells(r, colToGroupBy)), columnTotals
End If
' Sum up the new values
colValues(0) = CLng(colValues(0)) + CLng(sourceWorksheet.Cells(r, "C"))
colValues(1) = CLng(colValues(1)) + CLng(sourceWorksheet.Cells(r, "D"))
' Store the update totals
dict(CStr(sourceWorksheet.Cells(r, colToGroupBy))) = Join(colValues, ",")
Next
' Switch to the sheet to hold the summaries
targetWorksheet.Activate
' Clear the sheet
targetWorksheet.Rows.Delete
' Loop through each group and write out the summaries
For r = 0 To dict.Count - 1
colValues = Split(dict.Items(r), ",")
With targetWorksheet
.Cells(r + 1, 1) = dict.Keys(r)
.Cells(r + 1, 2) = colValues(0)
.Cells(r + 1, 3) = colValues(1)
End With
Next
End Sub
I also found a way to do it right in Excel. I used a bunch of SUMIF statements:
ie: =SUMIF(E6:E214,"K4",C6:C214)
So It searched Column E, rows 6:214 for the string K4 and then Summed up all the numbers in C6:C214 where it found K4.
So I was able to get all the L and K rates I needed.
I thank you very very much!
Safety Car
Joined: Jul 2007
Posts: 4,845
Likes: 145
That's pretty much my job. But VBA is painful for me to write in because it's older syntax and you have to do some stuff the hard way. On the other hand, you can cheat on other things that the newer languages enforce.
The problem is, sure, I can write all this code to brute force it, but then some Excel guru comes along and gives the solution in a single Excel formula. Then I look pretty lame.
The problem is, sure, I can write all this code to brute force it, but then some Excel guru comes along and gives the solution in a single Excel formula. Then I look pretty lame.
Thread
Thread Starter
Forum
Replies
Last Post
08_UA7_Gr33k
Member Cars for Sale
13
Feb 11, 2016 02:17 PM
InFaMouSLink
Car Parts for Sale
6
Oct 27, 2015 06:52 PM
08_UA7_Gr33k
Member Cars for Sale
1
Sep 27, 2015 01:56 PM
