VBA: playing with arrays and dictionaries

[Nelson78]

Hello everybody.

I've a situation as follows:

https://imgur.com/6FS0Fqw

I need to calculate how many different cities (column H) have code 233 (column D) and list aaa (column K)

The desired result is in cell N16 = 2, meaning the 2 records in green (in red the not considered duplicates).

I think I need a dictionary.

Thank's in advance.

 

[Jaafar Tribak]

you can use a formula in n16 such as :
=COUNTIFS(D1:D14,233,K1:K14,"aaa")

Or vba

Sub test()
    Sheet1.Range("N16") = Evaluate("=COUNTIFS(Sheet1!D1:D14,233,Sheet1!K1:K14,""aaa"")")
End Sub

 

[Nelson78]

No, using the solutions you've proposed, the result is 4 (it considers duplicates).
I need 2 (no duplicates).

 

[Nelson78]

you can use a formula in n16 such as :
=COUNTIFS(D1:D14,233,K1:K14,"aaa")

Or vba

Sub test()
    Sheet1.Range("N16") = Evaluate("=COUNTIFS(Sheet1!D1:D14,233,Sheet1!K1:K14,""aaa"")")
End Sub

Maybe something like this:

Sub count()
  
  Dim X, vRws
  Dim objDict As Object
  Dim lngRow As Long, lngLastRow

  Set objDict = CreateObject("Scripting.Dictionary")
  objDict.CompareMode = 1
  With Sheets(1)
    lngLastRow = .Range("K" & .Rows.Count).End(xlUp).Row
    vRws = Evaluate("row(2:" & lngLastRow & ")")
    X = Application.Index(.Cells, vRws, Array(4, 8, 11))  
  End With
  For lngRow = 1 To UBound(X, 1)
    objDict(X(lngRow, 1) & "|" & X(lngRow, 2) & "|" & X(lngRow, 3)) = 1
  Next


'??????????

end sub

Now, in

objDict

I have all the different combinations of columns D/H/K: how can I count the relevant ones?

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG03Jul53
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range("H2", Range("H" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Dn.Offset(, 3).Value = "aaa" And _
    Dn.Offset(, -4).Value = 233 [COLOR="Navy"]Then[/COLOR] .Item(Dn.Value) = Empty
[COLOR="Navy"]Next[/COLOR]
Range("N16") = .Count
[COLOR="Navy"]End[/COLOR] With

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Nelson78]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG03Jul53
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range("H2", Range("H" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Dn.Offset(, 3).Value = "aaa" And _
    Dn.Offset(, -4).Value = 233 [COLOR="Navy"]Then[/COLOR] .Item(Dn.Value) = Empty
[COLOR="Navy"]Next[/COLOR]
Range("N16") = .Count
[COLOR="Navy"]End[/COLOR] With

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

I'm adapting it to the real situation, and it seems to work fine.

But I'm bumping into a secondary problem: I've a button to trigger the macro in a different sheet that causes the following error:

run-time error '1004' Application-defined or Object-defined error

How can I handle it?

 

[MickG]

Try Modifying the "Set Rng" line to the below

With Sheets("Sheet1") ' Change to your sheet name instead of "sheet1"
   Set Rng = [COLOR="#FF0000"][SIZE=4].[/SIZE][/COLOR]Range("H2", [COLOR="#FF0000"][SIZE=4].[/SIZE][/COLOR]Range("H" & Rows.Count).End(xlUp))

End With

NB:- Don't forget the Dots in Red !!!!

 

[Jaafar Tribak]

I would love to see this done with an array formula alone ... I have tried but no success.

 

[RoryA]

You could use something like:

=COUNT(1/FREQUENCY(IF(K2:K14="aaa",IF(D2:D14=233,MATCH(H2:H14,H2:H14,0))),MATCH(H2:H14,H2:H14,0)))

entered normally.

 

[Jaafar Tribak]

You could use something like:

=COUNT(1/FREQUENCY(IF(K2:K14="aaa",IF(D2:D14=233,MATCH(H2:H14,H2:H14,0))),MATCH(H2:H14,H2:H14,0)))

entered normally.

I haven't dissected the formula to study it but a quick test gave me the wrong result - It always returns 1

Thank you.