Merge data when duplicate are found in first cell

[chiller]

Hi guys

stuck with this, Basically i have a excell sheet with these values in only first 3 columns shown( actual table has 33 headers)

[TABLE="width: 672"]
<tbody>[TR]
[TD]itemNumber[/TD]
[TD]Grouped or simple[/TD]
[TD]associated products[/TD]
[/TR]
[TR]
[TD]100.010UK _Group[/TD]
[TD]Grouped[/TD]
[TD]100.010UK[/TD]
[/TR]
[TR]
[TD]100.012UK _Group[/TD]
[TD]Grouped[/TD]
[TD]100.012UK[/TD]
[/TR]
[TR]
[TD]100.031UK _Group[/TD]
[TD]Grouped[/TD]
[TD]100.031AU[/TD]
[/TR]
[TR]
[TD]100.031UK _Group[/TD]
[TD]Grouped[/TD]
[TD]100.031UK[/TD]
[/TR]
[TR]
[TD]100.331UK _Group[/TD]
[TD]Grouped[/TD]
[TD]100.331UK[/TD]
[/TR]
[TR]
[TD]100.331UK _Group[/TD]
[TD]Grouped[/TD]
[TD]100.332UK[/TD]
[/TR]
[TR]
[TD]100.331UK _Group[/TD]
[TD]Grouped[/TD]
[TD]100.351UK[/TD]
[/TR]
[TR]
[TD]100.331UK _Group[/TD]
[TD]Grouped[/TD]
[TD]100.352UK[/TD]
[/TR]
[TR]
[TD]100.360UK _Group[/TD]
[TD]Grouped[/TD]
[TD]100.360UK[/TD]
[/TR]
[TR]
[TD]100.360UK _Group[/TD]
[TD]Grouped[/TD]
[TD]100.361UK[/TD]
[/TR]
[TR]
[TD]100.363UK _Group[/TD]
[TD]Grouped[/TD]
[TD]100.363UK[/TD]
[/TR]
[TR]
[TD]100.363UK _Group[/TD]
[TD]Grouped[/TD]
[TD]100.364UK[/TD]
[/TR]
</tbody>[/TABLE]

I want the values in first column to be merged with identical values, and for the values in associated products to be merged in same cell with a (, ) as a delimiter.please see example 2nd table below .




2nd table
[TABLE="width: 859"]
<tbody>[TR]
[TD]itemNumber[/TD]
[TD]Grouped or simple[/TD]
[TD]grouped [/TD]
[/TR]
[TR]
[TD]100.010UK _Group[/TD]
[TD]Grouped[/TD]
[TD]100.010UK,[/TD]
[/TR]
[TR]
[TD]100.012UK _Group[/TD]
[TD]Grouped[/TD]
[TD]100.012UK,[/TD]
[/TR]
[TR]
[TD]100.031UK _Group[/TD]
[TD]Grouped[/TD]
[TD]100.031AU,100.031UK,[/TD]
[/TR]
[TR]
[TD]100.331UK _Group[/TD]
[TD]Grouped[/TD]
[TD]100.331UK,100.332UK,100.351UK,100.351UK[/TD]
[/TR]
[TR]
[TD]100.360UK _Group[/TD]
[TD]Grouped[/TD]
[TD]100.360UK,100.361UK,[/TD]
[/TR]
[TR]
[TD]100.363UK _Group[/TD]
[TD]Grouped[/TD]
[TD]100.363UK,100.364UK,[/TD]
[/TR]
</tbody>[/TABLE]


then i need to strip all rows that have less than 2 sku in ("grouped" 2nd table)
so end table look like this

[TABLE="width: 859"]
<tbody>[TR]
[TD]itemNumber[/TD]
[TD]Grouped or simple[/TD]
[TD]grouped[/TD]
[/TR]
[TR]
[TD]100.031UK _Group[/TD]
[TD]Grouped[/TD]
[TD]100.031AU,100.031UK,[/TD]
[/TR]
[TR]
[TD]100.331UK _Group[/TD]
[TD]Grouped[/TD]
[TD]100.331UK,100.332UK,100.351UK,100.351UK,[/TD]
[/TR]
[TR]
[TD]100.360UK _Group[/TD]
[TD]Grouped[/TD]
[TD]100.360UK,100.361UK,[/TD]
[/TR]
[TR]
[TD]100.363UK _Group[/TD]
[TD]Grouped[/TD]
[TD]100.363UK,100.364UK,[/TD]
[/TR]
</tbody>[/TABLE]



Hope you can help.
Munir

 

[JLGWhiz]

This assumes that the file has been previously sorted so that duplicates are adjacent to each other vertically in column A. Since data deleted by code cannot be undone with the Excel undo facility, you are advised to test this on a copy or a mock up of your file before applying it to the original.

Sub grouper()
Dim sh As Worksheet, lr As Long
Set sh = Sheets(1) 'Edit sheet name
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
    For i = lr To 3 Step -1
        With sh
            If .Cells(i, 1) = .Cells(i - 1, 1) Then
                .Cells(i - 1, 3) = .Cells(i - 1, 3).Value & "," & .Cells(i, 3).Value
                Rows(i).Delete
            End If
        End With
    Next
    For j = sh.Cells(Rows.Count, 3).End(xlUp).Row To 2 Step -1
        If InStr(sh.Cells(j, 3).Value, ",") = 0 Then sh.Rows(j).Delete
    Next
End Sub

 

[chiller]

This works Perfectly, please excuse my delay in Gratitude.......

a)would it be possible to do a few tweaks to the file for example i would like to add the following extra rows with column header to every row.



also would it be possible to run this macro in Notepad++, because excell meeses up my formatting.



[TABLE="width: 322"]
<tbody>[TR]
[TD]brief_desc_1_header[/TD]
[TD] brief_desc_2_header[/TD]
[TD]brief_desc_3_header[/TD]
[/TR]
[TR]
[TD]Description[/TD]
[TD]Packed[/TD]
[TD]Pack Qty[/TD]
[/TR]
[TR]
[TD]Description[/TD]
[TD]Packed[/TD]
[TD]Pack Qty[/TD]
[/TR]
</tbody>[/TABLE]


b)also would it be possible to run this macro in Notepad++, because excel messes up my formatting.

c)would like the following values to be changed

in the (further_infomation) |eol| to be changed to 2-way speakers
notice the different opening and closers

this will allow the data in this column to be shown with bullet marks.

[TABLE="width: 319"]
<tbody>[TR]
[TD]further_infomation[/TD]
[TD]further_infomation[/TD]
[/TR]
[TR]
[TD]2-way speakers|eol|Magnetically shielded|eol|10cm (4") paper cone woofer with foam rubber surround|eol|5cm (2") paper cone tweeter with polycarbonate dome|eol|Mounting brackets supplied|eol|Spring clip terminal connections|eol|Supplied in pairs|eol|Available in black or white[/TD]
[TD]2-way speakers
Magnetically shielded
10cm (4") paper cone woofer with foam rubber surround
5cm (2") paper cone tweeter with polycarbonate dome
Mounting brackets supplied
Spring clip terminal connections
Supplied in pairs
Available in black or white

[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 137"]
<tbody>[TR]
[/TR]
</tbody>[/TABLE]

and change |tab| to

[TABLE="width: 128"]
<tbody>[TR]
[TD="width: 64"]auto_technical_info[/TD]
[TD="width: 64"]auto_technical_info[/TD]
[/TR]
[TR]
[TD]Power max.|tab|50W|eol|Frequency response|tab|65Hz - 20kHz|eol|Impedance|tab|6/8 Ohms|eol|Dimensions|tab|175 x 105 x 105mm|eol|Weight : Per set|tab|1.7kg[/TD]
[TD]Power max. 100W
Frequency response 200Hz - 15kHz
Impedance 8 ohms
SPL @ 1W/1m 85dB
System 2-way
Dimensions 205 x 102 x 100mm
Weight : Per set 2.5kg
[/TD]
[/TR]
</tbody>[/TABLE]