merging duplicate data into one cell

Punquey

New Member
Joined
Jul 11, 2017
Messages
8
So, I have a project. don't we all. I need to combine all known UPC codes for a specific part number into a single cell with a semi colon between each UPC code. The data I have is available like this:

[TABLE="width: 718"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]1X3 BN[/TD]
[TD]102141[/TD]
[/TR]
[TR]
[TD]1X3 BN[/TD]
[TD]94913880897[/TD]
[/TR]
[TR]
[TD]1X3 BN[/TD]
[TD]94913885892[/TD]
[/TR]
[TR]
[TD]1X3 BN[/TD]
[TD]10668197024406[/TD]
[/TR]
[TR]
[TD]1X3 BN[/TD]
[TD]20690308000129[/TD]
[/TR]
[TR]
[TD]1X3 BN[/TD]
[TD]40032888005486[/TD]
[/TR]
[TR]
[TD]1X3 BN[/TD]
[TD]40032888100457[/TD]
[/TR]
[TR]
[TD]1X3 BN[/TD]
[TD]585-030[/TD]
[/TR]
[TR]
[TD]1X3 BN[/TD]
[TD]6818716389800[/TD]
[/TR]
[TR]
[TD]1X3 BN[/TD]
[TD]94913885892[/TD]
[/TR]
[TR]
[TD]1X3 BN[/TD]
[TD]AT1X3 BN[/TD]
[/TR]
[TR]
[TD]1X3 BN[/TD]
[TD]BI1X3 BN[/TD]
[/TR]
</tbody>[/TABLE]


How would I combine all of the UPC codes for part number 1x3 BN into a single cell. Note that not all part numbers have same number of UPC codes, it can vary from 1-20. Any help is greatly appreciated. :)
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
How about
Code:
Sub MergeData()
   Dim Cl As Range
   
   With CreateObject("scripting.dictionary")
      For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
         If Not .exists(Cl.Value) Then
            .Add Cl.Value, Cl.Offset(, 1)
         Else
            .Item(Cl.Value).Value = .Item(Cl.Value).Value & "; " & Cl.Offset(, 1).Value
            Cl.Offset(, 1).ClearContents
         End If
      Next Cl
   End With
   Range("B:B").SpecialCells(xlBlanks).EntireRow.Delete
End Sub
 
Upvote 0
You just broke my brain. So you want me to go and add this code in the VBA design mode, but I'm not sure where.
 
Upvote 0
Thank you, I'm giving it a try. 150k items to go through so it may be a while :)

Gave me time to look at excelapalooza. Do you know if they have a calendar of events/classes yet?
 
Upvote 0
Thank you, I'm giving it a try. 150k items to go through so it may be a while :)
When working on that amount of data it's usually best to mention it.;)

Gave me time to look at excelapalooza. Do you know if they have a calendar of events/classes yet?
No idea, I've seen the ads on this site, but that's all.
 
Last edited:
Upvote 0
This should be quicker
Code:
Sub MergeData()
   Dim Ary As Variant, Nary As Variant, Ky As Variant
   Dim i As Long
   
   Ary = Range("A2", Range("B" & Rows.Count).End(xlUp))
   With CreateObject("scripting.dictionary")
      For i = 1 To UBound(Ary)
         If Not .exists(Ary(i, 1)) Then
            .Add Ary(i, 1), Ary(i, 2)
            Ary(i, 1) = ""
         Else
            .Item(Ary(i, 1)) = .Item(Ary(i, 1)) & "; " & Ary(i, 2)
         End If
      Next i
      i = 0
      ReDim Nary(1 To .Count, 1 To 2)
      For Each Ky In .keys
         i = i + 1
         Nary(i, 1) = Ky
         Nary(i, 2) = .Item(Ky)
      Next Ky
   End With
   Range("A2", Range("B" & Rows.Count).End(xlUp)).ClearContents
   Range("A2").Resize(i, 2).Value = Nary
End Sub
 
Upvote 0

Forum statistics

Threads
1,223,896
Messages
6,175,264
Members
452,627
Latest member
KitkatToby

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top