Hi Team,
Below code works and gives me correct output as per my requirement.
But I am not able to understand some part of the code ,
Can add comment plz. and how it is working ,here in items Dictionary objects are added.
line are :-
If Not dict.Exists(arr_Fruit(i, 1)) Then Set dict(arr_Fruit(i, 1)) = CreateObject("Scripting.Dictionary")
dict(arr_Fruit(i, 1))(arr_Fruit(i, 2)) = 1
and
If dict.Exists(c.Value) Then c.Offset(, 1).Value = Join(dict(c.Value).Keys(), "/")
Input Range("a1:C10"), Look up column F and expected output is in Column G.
Below code works and gives me correct output as per my requirement.
But I am not able to understand some part of the code ,
Can add comment plz. and how it is working ,here in items Dictionary objects are added.
line are :-
If Not dict.Exists(arr_Fruit(i, 1)) Then Set dict(arr_Fruit(i, 1)) = CreateObject("Scripting.Dictionary")
dict(arr_Fruit(i, 1))(arr_Fruit(i, 2)) = 1
and
If dict.Exists(c.Value) Then c.Offset(, 1).Value = Join(dict(c.Value).Keys(), "/")
VBA Code:
Option Explicit
Sub test1()
Dim dict As New Scripting.Dictionary
Dim i As Long
Dim arr_Fruit As Variant
arr_Fruit = Range("a2:c6").Value
'---------Add Data to Dictionary---------
dict.CompareMode = vbTextCompare
For i = LBound(arr_Fruit, 1) To UBound(arr_Fruit, 1)
If Not dict.Exists(arr_Fruit(i, 1)) Then Set dict(arr_Fruit(i, 1)) = CreateObject("Scripting.Dictionary")
dict(arr_Fruit(i, 1))(arr_Fruit(i, 2)) = 1
Next i
Dim c As Range
For Each c In Range("f2:f4")
If dict.Exists(c.Value) Then c.Offset(, 1).Value = Join(dict(c.Value).Keys(), "/")
Next c
End Sub
Input Range("a1:C10"), Look up column F and expected output is in Column G.
Book1 | |||||||||
---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | |||
1 | Country | Fruit | Color | Name | Fruit/color - Output | ||||
2 | India | Mango | Yellow | India | Mango | ||||
3 | Australia | Apple | Green | Australia | Apple | ||||
4 | England | Banana | Yellow | England | Banana/Mango | ||||
5 | England | Mango | Blue | ||||||
6 | England | Mango | Blue | ||||||
Sheet1 |