Criteria based counting

[cruise]

Hello to everyone,
I want to count the number of cases basing on 8 filters in excel and remaining unwanted data count should be at last. Six fixed columns and variable rows not fixed (dynamic range)
Can anybody help me in writing macro for this.

Many many thanks in advance...

[shrivallabha]

Hi Cruise,

Welcome to VBAX.

While your data looks clear, what should be the final result / outcome?

[Bob Phillips]

Use SUMPRODUCT


=SUMPRODUCT(--(B1:B17="Scott"),--(E1:E17=10),F1:F17)

=SUMPRODUCT(--(B1:B17="Scott"),--(C1:C17=10000),--(E1:E17=10),F1:F17)

etc., etc.

[cruise]

Dear Sribalabha,
Thanks for the reply.
My client wants only those data from 8 conditions in excel(that means for cond 1 : Scott Clerk 10---> emp scott who is a clerk in deptno 10 only,
cond 3-------->employee scott who is a salesman working in dept 10 getting sal 10000 , etc ). Whereever the following 8 cond are not true, I want those unwanted record at last separated by slash (Clerk/Sales/ADAM/Baylish/10000/13000/20=4).
I am really new to this macro. I am learing only through browsing. So I am unable to get proper logic. Plz help

[shrivallabha]

I think your requirement is almost similar (few columns here and there) to this requirement (http://www.vbaexpress.com/forum/www.vbaexpress.com/forum/showthread.php?p=263954).

If yes, then this is amended code.


Sub CombineData()
Dim objDic As Object
Dim sStr As String
Set objDic = CreateObject("Scripting.Dictionary")
objDic.Comparemode = vbTextCompare
For i = 2 To 10
sStr = Range("A" & i).Value & "/" & Range("B" & i).Value & "/" & Range("C" & i).Value _
& "/" & Range("D" & i).Value & "/" & Range("E" & i).Value
If objDic.exists(sStr) Then
objDic.Item(sStr) = objDic.Item(sStr) + Range("F" & i).Value
Else
objDic.Add sStr, Range("F" & i).Value
End If
Next i
With objDic
Range("H2").Resize(.Count, 2).Value = Application.Transpose(Array(.keys, .items))
End With
Set objDic = Nothing
End Sub

[cruise]

Many many thanks. Its working briliantly. But is it possible to keep this criteria inside that. I want to filter data based on 8 criteria only. becoz how to keep this if condition inside that.
Unwanted record are also not cumulatively added, instead counted individually.
Please help. Rows are always fluctuating.

[shrivallabha]

Your 8 criteria are confusing me. See below

Scott Clerk 10 3 Criteria
Scott Clerk 20 3 Criteria
Scott 10000 Sales 10 4 Criteria
Scott 1000 Sales 20 4 Criteria
Tiger NewYork Clerk 10 4 Criteria
Tiger NewYork Clerk 20 4 Criteria
Tiger Chicago Clerk 10 4 Criteria
Tiger Chicago Clerk 20 4 Criteria

Why do they have disparate criteria? Coding would be simpler with consistent criteria.

Do you need the rest to be defined or "Others" would suffice?

[cruise]

Thanks for your comment.
My client need record like this only. means he wants only record count for scott who is a clerk only working in dept no 10 or 20 only. So he wants this total elaboration in terms of title separted by space.i.e. Scott Clerk 10, Scott Clerk 20.
Similarly, he wants record for Scott only who is a sales working in deptno 10 or 20 having sal 10000 rupees. As this record are not available in excel output,
so a key work 'No cases' should be there. i.e. Scott 10000 Sales 10= No cases, Scott 1000 Sales 20=13

Is this possible.

[shrivallabha]

OK. Clients, who else?

8 Cases will be worked out by code. And the rest will be listed at the bottom separately.


Sub CombineData()
Dim objDic As Object
Dim sStr As String
Set objDic = CreateObject("Scripting.Dictionary")
objDic.Comparemode = vbTextCompare
'Defining 8 cases
objDic.Add "Scott Clerk 10", 0
objDic.Add "Scott Clerk 20", 0
objDic.Add "Scott 10000 Sales 10", 0
objDic.Add "Scott 10000 Sales 20", 0
objDic.Add "Tiger New York Clerk 10", 0
objDic.Add "Tiger New York Clerk 20", 0
objDic.Add "Tiger Chicago Clerk 10", 0
objDic.Add "Tiger Chicago Clerk 20", 0
For i = 2 To Range("A" & Rows.Count).End(xlUp).Row
If objDic.exists(Range("B" & i).Value & " " & Range("A" & i).Value & " " & Range("E" & i).Value) Then
objDic.Item(Range("B" & i).Value & " " & Range("A" & i).Value & " " & Range("E" & i).Value) = _
objDic.Item(Range("B" & i).Value & " " & Range("A" & i).Value & " " & Range("E" & i).Value) + _
Range("F" & i).Value
ElseIf objDic.exists(Range("B" & i).Value & " " & Range("C" & i).Value & " " & Range("A" & i).Value & " " & Range("E" & i).Value) Then
objDic.Item(Range("B" & i).Value & " " & Range("C" & i).Value & " " & Range("A" & i).Value & " " & Range("E" & i).Value) = _
objDic.Item(Range("B" & i).Value & " " & Range("C" & i).Value & " " & Range("A" & i).Value & " " & Range("E" & i).Value) + _
Range("F" & i).Value
ElseIf objDic.exists(Range("B" & i).Value & " " & Range("D" & i).Value & " " & Range("A" & i).Value & " " & Range("E" & i).Value) Then
objDic.Item(Range("B" & i).Value & " " & Range("D" & i).Value & " " & Range("A" & i).Value & " " & Range("E" & i).Value) = _
objDic.Item(Range("B" & i).Value & " " & Range("D" & i).Value & " " & Range("A" & i).Value & " " & Range("E" & i).Value) + _
Range("F" & i).Value
ElseIf objDic.exists(Range("A" & i).Value & "/" & Range("B" & i).Value & "/" & Range("C" & i).Value _
& "/" & Range("D" & i).Value & "/" & Range("E" & i).Value) Then
objDic.Item(Range("A" & i).Value & "/" & Range("B" & i).Value & "/" & Range("C" & i).Value _
& "/" & Range("D" & i).Value & "/" & Range("E" & i).Value) = objDic.Item(Range("A" & i).Value & "/" & Range("B" & i).Value & "/" & Range("C" & i).Value _
& "/" & Range("D" & i).Value & "/" & Range("E" & i).Value) + Range("F" & i).Value
Else
objDic.Add Range("A" & i).Value & "/" & Range("B" & i).Value & "/" & Range("C" & i).Value _
& "/" & Range("D" & i).Value & "/" & Range("E" & i).Value, Range("F" & i).Value
End If
Next i
With objDic
Range("H2").Resize(.Count, 2).Value = Application.Transpose(Array(.keys, .items))
End With
Set objDic = Nothing
End Sub

[cruise]

Just a big thank you, for the as always promt, speedy and excellent work u provided. God bless you Sri.....


One small minor comment. Can we keep the value for the criteria in one column instead two i.e. Scott Clerk 10=5 in the same column (in stead criteria in one col and value in another col.).

Thanks once again