Remove duplicates and match two columns

arjun0405

New Member
Joined
Apr 30, 2018
Messages
5
Hi ppl,

I need to convert the Table 1 to Table 2 as shown below. Is it possible?

Table 1:
CountryProduct
France
A
FranceB
FranceC
GermanyB
GermanyC
USAA
USAA

<tbody>
</tbody>

Table 2:
CountryProduct
FranceA, B, C
GermanyB, C
USAA, A

<tbody>
</tbody>


Thanks in advance

Regards
Arjun
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,428
Office Version
365
Platform
Windows
Hi & welcome to the board.
How about
Code:
Sub CombineProduct()
   Dim Dic As Object
   Dim Cl As Range
   Dim Ky As Variant
   
   Set Dic = CreateObject("scripting.dictionary")
   For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
      If Not Dic.exists(Cl.Value) Then
         Dic.Add Cl.Value, Cl.Offset(, 1).Value
      Else
         Dic.Item(Cl.Value) = Dic.Item(Cl.Value) & ", " & Cl.Offset(, 1).Value
      End If
   Next Cl
   For Each Ky In Dic.keys
      With Sheets("sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
         .Value = Ky
         .Offset(, 1).Value = Dic(Ky)
      End With
   Next Ky
End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,428
Office Version
365
Platform
Windows
You're welcome
 

Forum statistics

Threads
1,082,548
Messages
5,366,227
Members
400,880
Latest member
dwb

Some videos you may like

This Week's Hot Topics

Top