Hi Team,
I am trying to print Key's and Items
Column A is (Country) a Key.
if Country comes twice as key but it has different fruits Name.
Then fruit should be combined with Seprator, as shown in Table.
Below is a Table with expected value is Column K.
Thanks
mg
I am trying to print Key's and Items
Column A is (Country) a Key.
if Country comes twice as key but it has different fruits Name.
Then fruit should be combined with Seprator, as shown in Table.
Below is a Table with expected value is Column K.
VBA Code:
Sub test()
Dim dict As New Scripting.Dictionary
Dim arr_Fruit As Variant
arr_Fruit = Range("A1").CurrentRegion.Value2
dict.RemoveAll
'---------Add Data to Dictionary---------
With dict
.CompareMode = TextCompare
For i = LBound(arr_Fruit, 1) To UBound(arr_Fruit, 1)
If Not .Exists(arr_Fruit(i, 1)) Then
.Add arr_Fruit(i, 1), Array(arr_Fruit(i, 2), arr_Fruit(i, 3))
Else
.Item(arr_Fruit(i, 1)) = .Item(arr_Fruit(i, 1))(0) & "/" & arr_Fruit(i, 2)
End If
Next i
Dim c As Range
For Each c In Range("f2:f4")
If dict.Exists(c.Value) Then
c.Offset(, 1).Value = .Item(c.Value)(0)
End If
Next c
End With
End Sub
Book12 | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | |||
1 | Country | Fruit | Color | Name | Fruit/color | Expected | ||||||
2 | India | Mango | Yellow | India | Mango | Name | Fruit/color | |||||
3 | Australia | Apple | Green | Australia | Mango | India | Mango | |||||
4 | England | Banana | Yellow | England | Australia | Mango | ||||||
5 | England | Mango | Blue | England | Banana/Mango | |||||||
6 | ||||||||||||
Sheet1 |
Thanks
mg
Last edited: