vba - collection/dictionary unique value with complete range

[Mallesh23]

Hi Team

I need your help in extracting Unique Trade ID and paste in range F2.
But also along with that I need complete data range infrrnt of it.
Looking your help in dictionary/Collection attached expected result.

through below I can extract single column.

Sub Unique()
    Dim lr As Long
    lr = Range("a1").CurrentRegion.Rows.Count
    Dim str As String
    Dim i As Integer
   
        Dim dict As New Scripting.Dictionary
   
        For i = 2 To lr
            str = Cells(i, 1).Value
          
           If Not dict.Exists(str) Then
            dict.Add (str), 1
          
           End If
   
        Next i
               
        Range("f2").Resize(dict.Count).Value = WorksheetFunction.Transpose(dict.Keys)
       
End Sub

 

[Norie]

What exactly do you want to return for each unique TradeID?

In the image you posted all you appear to be returning is the data from the 2 columns to the right of where you find the first instance of each unique TradeID.

 

[Peter_SSs]

I also suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version.

 

[Mallesh23]

Hi Norie,

I want unique trade ID, once found, I want to add below range to collection as these are unique
Range("A2").Resize(, 3).copy
Range("A3").Resize(, 3).copy
Range("A5").Resize(, 3).copy

and want to paste collections in range(f2")

Thanks for your help in advance!



Regards,
mg

 

[Norie]

You could change this,

dict.Add (str), 1

to this

dict Add str, Cells(i, 2).Resize.Value

Then change the code to populate the results to something like this.

For Each ky In dict.Keys
    Range("F" & Rows.Count).End(xlUp).Value = ky
    Range("G" & Rows.Count).Resize(,2).End(xlUp).Value = dict(ky)
Next ky

 

[Mallesh23]

Hi Norie,

Thank you so much, it worked as expected.?

Regards,
mg