Merging cells and retaining data

[Tom Allen]

post_title <tbody> </tbody>	post_excerpt <tbody> </tbody>	post_status <tbody> </tbody>	visibility <tbody> </tbody>	stock_status <tbody> </tbody>	regular_price <tbody> </tbody>	taxroduct_cat <tbody> </tbody>	attribute: pa_model <tbody> </tbody>	attribute: pa_alternative-part-numbers <tbody> </tbody>	attribute: pa_part-type <tbody> </tbody>
example product A <tbody> </tbody>	screw	publish <tbody> </tbody>	visible <tbody> </tbody>	instock <tbody> </tbody>	0.18 <tbody> </tbody>	Commander	Commander		Screw-Bottom
example product A <tbody> </tbody>	screw	publish <tbody> </tbody>	visible <tbody> </tbody>	instock <tbody> </tbody>	0.18 <tbody> </tbody>	Highway 3	Highway 3		Screw-Bottom
example product A <tbody> </tbody>	screw	publish <tbody> </tbody>	visible <tbody> </tbody>	instock <tbody> </tbody>	0.18 <tbody> </tbody>	Highway	Highway		Screw-Bottom
example product B	blade	publish <tbody> </tbody>	visible <tbody> </tbody>	instock <tbody> </tbody>	20	Parkway	Parkway		blade7
example product B	blade	publish <tbody> </tbody>	visible <tbody> </tbody>	instock <tbody> </tbody>	20	Parkway2	Parkway2		blade7
example product C	disc	publish <tbody> </tbody>	visible <tbody> </tbody>	instock <tbody> </tbody>	15	Mounted	Mounted		blade10
example product C	disc	publish <tbody> </tbody>	visible <tbody> </tbody>	instock <tbody> </tbody>	15	Mounted5	Mounted5		blade10
example product C	disc	publish <tbody> </tbody>	visible <tbody> </tbody>	instock <tbody> </tbody>	15	Mounted9	Mounted9		blade11
example product C	disc	publish <tbody> </tbody>	visible <tbody> </tbody>	instock <tbody> </tbody>	15	Motor7	Motor7		blade15

<tbody>
</tbody>

Hi I've got a set of product data, as demonstrated above. I somehow need to merge the products if they are duplicated while retaining certain data.

So for example if example product A is found more than once then I need to merge all instances of example product A into one row.

The taxroduct_cat column and attribute: pa_model column will always contain different values for each instance of the same product. I can just replicate the attribute: pa_part-type column from the taxroduct_cat column so that cab be ignored. However I need to merge the taxroduct_cat column and retain all values.

So in the example data shown above for example product A the taxroduct_cat column would merge to show Commander|Highway 3|Highway

The attribute: pa_part-type column will sometimes be different as well. However I only need to keep the values if they are different. For example the example product A row would just show Screw-Bottom as they are all the same. However example product C would show blade10|blade11|blade15

Below is an example of how the above data should look

post_title <tbody> </tbody>	post_excerpt <tbody> </tbody>	post_status <tbody> </tbody>	visibility <tbody> </tbody>	stock_status <tbody> </tbody>	regular_price <tbody> </tbody>	taxroduct_cat <tbody> </tbody>	attribute: pa_model <tbody> </tbody>	attribute: pa_alternative-part-numbers <tbody> </tbody>	attribute: pa_part-type <tbody> </tbody>
example product A <tbody> </tbody>	screw	publish <tbody> </tbody>	visible <tbody> </tbody>	instock <tbody> </tbody>	0.18 <tbody> </tbody>	Commander|Highway 3|Highway	Commander|Highway 3|Highway		Screw-Bottom
example product B	blade	publish <tbody> </tbody>	visible <tbody> </tbody>	instock <tbody> </tbody>	20	Parkway|Parkway2	Parkway|Parkway2		blade7
example product C	disc	publish <tbody> </tbody>	visible <tbody> </tbody>	instock <tbody> </tbody>	15	Mounted|Mounted5|Mounted9|Motor7	Mounted|Mounted5|Mounted9|Motor7		blade10|blade11|blade15

<tbody>
</tbody>

I'm usually pretty good at finding similar posts and copying or tweaking code for my needs but I'm not even sure where to start on this one. There will be thousands of products so a VBA script to acheive this would be ideal.

Any assistance would be greatly appreciated.

 

[MickG]

Try this:-
NB:- This code will update and remove unwanted rows from your Data.

[COLOR="Navy"]Sub[/COLOR] MG07Jun36
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] nRng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Not .Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
        .Add Dn.Value, Dn
    [COLOR="Navy"]Else[/COLOR]
       [COLOR="Navy"]If[/COLOR] InStr("#" & .Item(Dn.Value).Offset(, 6).Value & "#", "#" & Dn.Offset(, 6).Value & "#") = 0 [COLOR="Navy"]Then[/COLOR]
                .Item(Dn.Value).Offset(, 6).Value = _
                .Item(Dn.Value).Offset(, 6).Value & ", " & Dn.Offset(, 6).Value
        [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]If[/COLOR] InStr("#" & .Item(Dn.Value).Offset(, 7).Value & "#", "#" & Dn.Offset(, 7).Value & "#") = 0 [COLOR="Navy"]Then[/COLOR]
                .Item(Dn.Value).Offset(, 7).Value = _
                .Item(Dn.Value).Offset(, 7).Value & ", " & Dn.Offset(, 7).Value
         [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]If[/COLOR] InStr("#" & .Item(Dn.Value).Offset(, 9).Value & "#", "#" & Dn.Offset(, 9).Value & "#") = 0 [COLOR="Navy"]Then[/COLOR]
             .Item(Dn.Value).Offset(, 9).Value = _
             .Item(Dn.Value).Offset(, 9).Value & ", " & Dn.Offset(, 9).Value
          [COLOR="Navy"]End[/COLOR] If
            [COLOR="Navy"]If[/COLOR] nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
                 [COLOR="Navy"]Set[/COLOR] nRng = Dn
            [COLOR="Navy"]Else[/COLOR]
                [COLOR="Navy"]Set[/COLOR] nRng = Union(nRng, Dn)
            [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]If[/COLOR] Not nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR] nRng.EntireRow.Delete
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Tom Allen]

Hi Mick, thanks for the reply. That works great.