• 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.

Sum criteria wise & update

Abhijeet

Active Member
Hi
I have data in excel in column A Days in B Column Date in C column C Units in D column Assignments i want Sum of units with assignment number If Weekday then update that in Sheet 2 Allowance Type Weekday & Element On call If weekend Then Allowance type Weekend & Element On call in Units column update the Sum.In column F:I Saturday,Sunday,Weekday,Bank Holiday below these headers any value update as .If Saturday & weekday any value that sum update in sheet2 in Allowance Type Emer 1.5 & Element Update as Emer.If Sunady or Bank Holiday Column any Value of sum Update as in Allowance Type Emer 2 & Element as Emer.Please help me and Give me macro for this
 

Attachments

  • Element wise update.xlsx
    9.8 KB · Views: 8

Hi,

you can use a loop to read data in CurrentRegion
and worksheet function Match to sum in result worksheet …

Another way is to use Dictionary object …
 
This is what i got.

Code:
Sub Abhijeet_20499()
Dim rc As Long

Range("L1").CurrentRegion.ClearContents

[Criteria].AdvancedFilter Action:=xlFilterCopy, _
  CriteriaRange:=[Criteria], CopyToRange:=[Extract], Unique:=True

rc = Application.CountA(Columns("L"))

Range(Cells(rc + 1, "L"), Cells(rc + rc - 1, "L")).Value = _
Range(Cells(2, "L"), Cells(rc, "L")).Value

Range(Cells(2, "M"), Cells(rc + rc - 1, "M")) = "On Call"
Range(Cells(2, "N"), Cells(rc, "N")) = "Weekday"
Range(Cells(2, "O"), Cells(rc, "O")) = _
"=SUMIFS($C$2:$C$14,$D$2:$D$14,L2,$A$2:$A$14,""<>""&TEXT(0,""DDDD""),$A$2:$A$14,""<>""&TEXT(1,""DDDD""))"

Range(Cells(rc + 1, "N"), Cells(rc + rc - 1, "N")).Value = "Weekend"
Range(Cells(rc + 1, "O"), Cells(rc + rc - 1, "O")) = _
    "=SUMPRODUCT((MATCH($A$2:$A$14,{""Monday"",""Tuesday"",""Wednesday""," & _
    """Thursday"",""Friday"",""Saturday"",""Sunday""},0)>5)*($D$2:$D$14=L2),$C$2:$C$14)"
[l1:O1] = Array("Assignment", "Element", "Allowance", "Units")
Range("L1").CurrentRegion.Value = Range("L1").CurrentRegion.Value
Columns("l:O").AutoFit
End Sub

Let me know how testy it is.
 

Attachments

  • Element wise update.xlsm
    18.6 KB · Views: 3
This macro gives only for on call Not update Emer Element Data Please give that also.Please give me Dynamic Range also if data is more then this macro will not pull so please give me dynamic range
 
Last edited:
This macro gives only for on call Not update Emer Element Data Please give that also.Please give me Dynamic Range also if data is more then this macro will not pull so please give me dynamic range

You might not read my post clearly as i have written that "This is what i got".
What i posted is prosposnal to what i understand on your data.I will loop soon for remaining.

Range are as per your sheet named ranges. I will make sure to make it robust on next post. So, Stay tuned & be awesome.
 
Back
Top