vba help, sum with dictionary exclude blank/#N/A/string value

[Mallesh23]

Hi Team,

While summing data in dictionary, sometimes I come across with string/Blank/#N/A value in column.
how to exclude such type of cells values.

Sub SumUsing_Dictionary_ExcludeBlank_String

()

    Dim arr As Variant
    arr = Range("A1").CurrentRegion.Value2
  
    Dim temp As Variant
  
    Dim dict As New Scripting.Dictionary

Dim i As Long

    With dict
        For i = LBound(arr) To UBound(arr)
                  
            If Not .Exists(arr(i, 2)) Then
                .Add arr(i, 2), Array(arr(i, 3), arr(i, 4))
            Else
                temp = .Item(arr(i, 2))
                temp(0) = temp(0) + .Item(arr(i, 3))
                temp(1) = temp(1) + .Item(arr(i, 4))
                 .Item(arr(i, 2)) = Temp             
            End If
      
        Next i

  
    Dim c As Range
  
    For Each c In Range("K2:K4")
        If .Exists(c.Value) Then
            c.Offset(, 1).Value = .Item(c.Value)(0)
            c.Offset(, 2).Value = .Item(c.Value)(1)
        End If
  
    Next c
      
    End With
  
  
 
End Sub

Below is a table with expected values are in H and I Columns.

Book17
	A	B	C	D	E	F	G	H	I
1	Date	Player	Commissioned	Salary Received			Player	Commissioned	Salary Received
2	22/10/2020	Sachin	1000	4000			Sachin	3000	15000
3	23/10/2020	Dhoni	1000	5000			Dhoni	2000	20000
4	24/10/2020	Virat	1000	xxxx			Virat	2000	9000
5	25/10/2020	Sachin	1000	4000
6	26/10/2020	Dhoni	1000	7000
7	26/10/2020	Dhoni	#N/A	8000
8	26/10/2020	Sachin	1000	6000
9	26/10/2020	Sachin		1000
10	26/10/2020	Virat	1000	9000
Sheet1

Thanks
mg

 

[mohadin]

Hi
Try to replace with

With dict
        For i = LBound(arr) + 1 To UBound(arr)
                  
            If Not .Exists(arr(i, 2)) Then
            
                .Add arr(i, 2), Array(IIf(Application.IsNumber(arr(i, 3)), arr(i, 3), 0), IIf(Application.IsNumber(arr(i, 4)), arr(i, 4), 0))
            Else
            
                temp = .Item(arr(i, 2))
                temp(0) = temp(0) + IIf(Application.IsNumber(arr(i, 3)), arr(i, 3), 0)
                temp(1) = temp(1) + IIf(Application.IsNumber(arr(i, 4)), arr(i, 4), 0)
                 .Item(arr(i, 2)) = temp
            End If
      
        Next i

 

[Mallesh23]

Hi Mohadin,

Superrbbb ! very nice , it worked. thanks a lot for your help ! ?



Thanks
mg

 

[mohadin]

You Are Welcome
Thank you for the feedback
Be Happy

 

[Mallesh23]

Hi Mohadin,

If I would like to add date column in dictionary items.
What will be the code, as shown in Column J. Any date is ok, first occcurance date or last occurance date from column J.

Below is a table with expected output in Column G onward.

Book17
	A	B	C	D	E	F	G	H	I	J
1	Date	Player	Commissioned	Salary Received	Payment Date		Player	Commissioned	Salary Received	Payment Date
2	22/10/2020	Sachin	1000	4000	22/10/2020		Sachin	3000	15000	22/10/2020
3	23/10/2020	Dhoni	#N/A	5000	23/10/2020		Dhoni	2000	20000	23/10/2020
4	24/10/2020	Virat	1000	5000	24/10/2020		Virat	2000	9000	24/10/2020
5	25/10/2020	Sachin	1000		25/10/2020
6	26/10/2020	Dhoni	1000	7000	26/10/2020
7	26/10/2020	Dhoni	1000	8000	26/10/2020
8	26/10/2020	Sachin	xxx	6000	26/10/2020
9	26/10/2020	Sachin	1000	1000	26/10/2020
10	26/10/2020	Virat	1000	9000	26/10/2020
Sheet1

Thanks
mg

 

[mohadin]

 With dict
        For i = LBound(arr) + 1 To UBound(arr)
            If Not .Exists(arr(i, 2)) Then
                .Add arr(i, 2), Array(IIf(Application.IsNumber(arr(i, 3)), arr(i, 3), 0), IIf(Application.IsNumber(arr(i, 4)), arr(i, 4), 0), arr(i, 1))
            Else
                temp = .Item(arr(i, 2))
                temp(0) = temp(0) + IIf(Application.IsNumber(arr(i, 3)), arr(i, 3), 0)
                temp(1) = temp(1) + IIf(Application.IsNumber(arr(i, 4)), arr(i, 4), 0)
                .Item(arr(i, 2)) = temp
            End If
        Next i
        Dim c As Range
        For Each c In Range("K2:K4")
            If .Exists(c.Value) Then
                c.Offset(, 1).Value = .Item(c.Value)(0)
                c.Offset(, 2).Value = .Item(c.Value)(1)
                c.Offset(, 2).Value = .Item(c.Value)(2)
            End If
        Next c
    End With

 

[Mallesh23]

Hi Mohadin,

Thanks again for your help.
What this line means :=> .Item(arr(i, 2)) = temp

from below code

Else
                temp = .Item(arr(i, 2))
                temp(0) = temp(0) + IIf(Application.IsNumber(arr(i, 3)), arr(i, 3), 0)
                temp(1) = temp(1) + IIf(Application.IsNumber(arr(i, 4)), arr(i, 4), 0)
                .Item(arr(i, 2)) = temp

Thanks
mg

 

[mohadin]

You started with
temp = .Item(arr(i, 2))
Then you did some calculations to the content of temp witch is originally the item of arr(i,2)
when you finish you would put the result back to where it belongs, Right?

 

[Mallesh23]

Hi Mohadin,

oh yes, understood, perfect ! thanks ?


Thanks
mg

 

[mohadin]

You are welcome