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)

itemNumber	Grouped or simple	associated products
100.010UK _Group	Grouped	100.010UK
100.012UK _Group	Grouped	100.012UK
100.031UK _Group	Grouped	100.031AU
100.031UK _Group	Grouped	100.031UK
100.331UK _Group	Grouped	100.331UK
100.331UK _Group	Grouped	100.332UK
100.331UK _Group	Grouped	100.351UK
100.331UK _Group	Grouped	100.352UK
100.360UK _Group	Grouped	100.360UK
100.360UK _Group	Grouped	100.361UK
100.363UK _Group	Grouped	100.363UK
100.363UK _Group	Grouped	100.364UK

<tbody>
</tbody>

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

itemNumber	Grouped or simple	grouped
100.010UK _Group	Grouped	100.010UK,
100.012UK _Group	Grouped	100.012UK,
100.031UK _Group	Grouped	100.031AU,100.031UK,
100.331UK _Group	Grouped	100.331UK,100.332UK,100.351UK,100.351UK
100.360UK _Group	Grouped	100.360UK,100.361UK,
100.363UK _Group	Grouped	100.363UK,100.364UK,

<tbody>
</tbody>

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

itemNumber	Grouped or simple	grouped
100.031UK _Group	Grouped	100.031AU,100.031UK,
100.331UK _Group	Grouped	100.331UK,100.332UK,100.351UK,100.351UK,
100.360UK _Group	Grouped	100.360UK,100.361UK,
100.363UK _Group	Grouped	100.363UK,100.364UK,

<tbody>
</tbody>

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.

brief_desc_1_header	brief_desc_2_header	brief_desc_3_header
Description	Packed	Pack Qty
Description	Packed	Pack Qty

<tbody>
</tbody>

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.

further_infomation	further_infomation
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	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

<tbody>
</tbody>

<tbody>
</tbody>

and change |tab| to

auto_technical_info	auto_technical_info
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	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

<tbody>
</tbody>