CODEWITHSUNDEEP
vbaexcel
Szymon
Szymon

Reputation: 1

Excel VBA, loop that sums and then counts anew with each new value

For each employee in a shop there was a task. If employee completed the task it has '1' in the 'Task' column attached to the 'Shop'. I have to sum all the '1' for each shop and check the total percentage of task completion for each shop. Example given below:

Shop  Task
1002    0
1002    1
1002    0
1002    0
1002    0
1008    1
1008    1
1008    1
1008    1
etc...

I don't know how to create a loop that completes the sum and percentage that changes with every 'Shop' change. Help much appreciated.

Upvotes: 0

Views: 205

Answers (4)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60214

Given how you are entering your data (1's and 0's), you can just use a Pivot Table.

Drag Shops to the rows area, and Tasks to the Values area twice. For the first instance use SUM For the % completed use AVERAGE

enter image description here

Upvotes: 0

Whome
Whome

Reputation: 10400

This is an example loop how you could use dictionary object and variant array. Sometimes it is easier to implement a business logic in VBA 100% code. You could do all kind of things while looping rows. This example just print outs [0]total,[1]=countIfOne values per Key array.

   ' Tools/References: [x]Microsoft Scripting Runtime
    Public Sub sumShops()
        Dim ws As Worksheet
        Dim arr As Scripting.Dictionary
        Dim iRow As Long
        Dim val As String
        Dim item As Variant

        Set arr = New Scripting.Dictionary
        Set ws = Application.Worksheets("Shops")
        For iRow = 2 To ws.UsedRange.Rows.Count
            val = Trim(ws.Cells(iRow, 1)) ' 1001,1002,..
            If Not arr.Exists(val) Then
                ReDim item(0 To 1) As Long
                item(0) = 0 ' total count
                item(1) = 0 ' count if task=1
                Call arr.Add(val, item)
            Else
                item = arr.item(val)
            End If
            item(0) = item(0) + 1
            If ws.Cells(iRow, 2).Value = "1" Then item(1) = item(1) + 1 ' count task=1 rows
            ' we must reference array back to the dictionary key
            arr(val) = item
        Next

        ' Loop dictionary by keys and print an array(0..1)
        For iRow = 0 To arr.Count - 1
            val = arr.Keys(iRow)
            item = arr.item(val)
            Debug.Print val & "=" & item(0) & "," & item(1)
        Next
    End Sub

Upvotes: 0

user4039065
user4039065

Reputation:

I doubt whether this is actually faster than SUMIF but it does collect the shop identifiers as well as the totals in one pass.

sub shopTotals()

    dim i as long, arr as variant, dict as object

    set dict = createobject("scripting.dictionary")
    dict.comparemode = vbtextcompare

    with worksheets("sheet1")

        arr = .range(.cells(2, "A"), .cells(.rows.count, "B").end(xlup)).value2

        for i=lbound(arr, 1) to ubound(arr, 1)
            dict.item(arr(i, 1)) = dict.item(arr(i, 1)) + arr(i, 2)
        next i

        .cells(2, "D").resize(dict.count, 1) = application.transpose(dict.keys)
        .cells(2, "E").resize(dict.count, 1) = application.transpose(dict.items)

    end with

end sub

AVERAGEIF or COUNTIF can retrieve other stats.

Upvotes: 1

Solar Mike
Solar Mike

Reputation: 8375

You could use data validation to select which shop you want to work with and sumifs() to do the calculation, see: enter image description here

Upvotes: 0

Related Questions