VBA Help - dictionary highlight new Entry

[Mallesh23]

Hi Team,

I am comparing two list and bringing Unique values.
I am able to get output of unique keys, But how to get Dictionary Items of that unique key as well. Thanks

I am storing data into Array as my data are different workbooks.


Below attempted code, gives correct unique keys except Items, how to get it.

Sub Dictionary_Find_New_Entry()
    
    ary_Compare = Range("f2:f4").Value2
    ary_base = Range("A2:b5").Value2
    
        
    Dim dict As New Scripting.Dictionary
    Dim i As Long
    
    
     With dict
            .CompareMode = TextCompare
            For i = LBound(ary_base) To UBound(ary_base)
                .Item(ary_base(i, 2)) = "Empty"
                   If .Item(ary_base(i, 2)) <> "" Then
                      If Not .Exists(ary_base(i, 2)) Then
                            .Add (ary_base(i, 2)), (ary_base(i, 1))
                        End If
                    End If
            Next i


            For i = LBound(ary_Compare) To UBound(ary_Compare)
                If .Exists(ary_Compare(i, 1)) Then .Remove (ary_Compare(i, 1))
            Next i

    End With
    
    Range("J1").value =  dict.Keys(0)  ' Giving correct output :=> Goods damaged in Transit
    
    Range("I1").value dict.Items(0)   :=> should give [B]37133[/B]
    

End Sub

Below is a Table , expected output I & j Columns

Book2
	A	B	C	D	E	F	G	H	I	J
1	Invoice no	Comments				Comment			Expected Output	Key-Dictionary
2	14656	Goods in Wareshouse				Goods in Wareshouse			37133	Goods damaged in Transit
3	27524	Goods Returned				goods Returned
4	37133	Goods damaged in Transit				Goods Sold
5	23427	Goods Sold
Sheet1

Thanks
mg

 

[Yongle]

@Mallesh23
- is there a reason why you have not replied to VBA Help - Dictionary to seperate date ?
- you are trying to achieve something VERY similar here
- the solution provided in the other thread could be adapted to solve this problem

 

[Mallesh23]

Hi Yongle,

I was busy with some other office task, hence I was delay in replying,
But your code was really nice . I liked it the solution provided in dictionary.

As I am still learning dictionary. still I can't make code in dictionary independently.

So is it possible to make changes in existing code to get dictionary item.


Thanks
mg

 

[Yongle]

Same code as in prior solution

Sub NewEntry()
    Dim OldDict As Object, arrIn As Variant, arrOld As Variant, arrNew() As Variant, a As Long, r As Long
    Set OldDict = CreateObject("Scripting.Dictionary")
    arrIn = Range("A1:B5").Value
    arrOld = Range("F2:F4").Value
'prior values
    With OldDict
         .CompareMode = vbTextCompare
        For a = 1 To UBound(arrOld)
            If Not .Exists(arrOld(a, 1)) Then OldDict.Add arrOld(a, 1), arrOld(a, 1)
        Next a
'get new values
        ReDim arrNew(1 To UBound(arrIn), 1 To 2)
        For a = 1 To UBound(arrIn)
            If Not .Exists(arrIn(a, 2)) Then
                r = r + 1
                arrNew(r, 1) = arrIn(a, 1)
                arrNew(r, 2) = arrIn(a, 2)
            End If
        Next a
    End With
'write to sheet
    Range("I1").Resize(UBound(arrIn), 2).Value = arrNew
End Sub

 

[Yongle]

PLEASE NOTE:
I made a later amendment to code in post#4 to allow for

 .CompareMode = vbTextCompare

 

[Mallesh23]

Hi Yongle,

Perfect ! gives correct output .?

I am still curious , in my attempted code are there any chances we get invoice no as well from it.

Just for learning purpose not an urgent.


Thanks
mg

 

[Yongle]

Which invoice numbers? ?
There is only one item and the invoice number is in the result

 

[Mallesh23]

Hi Yongle,

Column is F is my Master Criteria , Column A and B Are my input Columns here new Comments may come.
Task is I have to identify Unique comments...... and its invoice no of that Comment.
So that user can update in Master F Criteria List.

If you Remove HardCoded Range("I2:J2") value from my Table.
and Run my attempted code. you will get the result. of dictionary but not Invoice.


Range("J2").value = dict.Keys(0) ' Giving correct output :=> Goods damaged in Transit
Range("I2").value dict.Items(0) :=> should give 37133


Thanks
mg

 

[Yongle]

The code returns both values with the data you supplied. Is the data you supplied not correct?

 

[Fluff]

if you delete the lines in red, then your original code should work

           For i = LBound(ary_base) To UBound(ary_base)
                .Item(ary_base(i, 2)) = "Empty"
                   If .Item(ary_base(i, 2)) <> "" Then
                      If Not .Exists(ary_base(i, 2)) Then
                            .Add (ary_base(i, 2)), (ary_base(i, 1))
                        End If
                    End If
            Next i

You are adding a key & putting nothing in the item, you are then checking to see if the item is not empty (which cannot be), so this line is never checked If Not .Exists(ary_base(i, 2)) Theneven if that line did run, it would always return false, as you have just added that key to the dictionary.