Merging cells and retaining data

T

Tom Allen

Board Regular
Joined
Sep 26, 2014
Messages
92
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]post_title[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]post_excerpt[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]post_status[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]visibility[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]stock_status[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]regular_price[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 268"]
<tbody>[TR]
[TD="width: 268"]tax:product_cat[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]attribute: pa_model[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]attribute: pa_alternative-part-numbers[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]attribute: pa_part-type[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]example product A[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]screw[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]publish[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]visible[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]instock[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: center"]0.18[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Commander[/TD]
[TD]Commander[/TD]
[TD][/TD]
[TD]Screw-Bottom[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]example product A[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]screw[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]publish[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]visible[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]instock[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: center"]0.18[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Highway 3[/TD]
[TD]Highway 3[/TD]
[TD][/TD]
[TD]Screw-Bottom[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]example product A[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]screw[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]publish[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]visible[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]instock[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: center"]0.18[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Highway[/TD]
[TD]Highway[/TD]
[TD][/TD]
[TD]Screw-Bottom[/TD]
[/TR]
[TR]
[TD]example product B[/TD]
[TD]blade[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]publish[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]visible[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]instock[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]20[/TD]
[TD]Parkway[/TD]
[TD]Parkway[/TD]
[TD][/TD]
[TD]blade7[/TD]
[/TR]
[TR]
[TD]example product B[/TD]
[TD]blade[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]publish[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]visible[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]instock[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]20[/TD]
[TD]Parkway2[/TD]
[TD]Parkway2[/TD]
[TD][/TD]
[TD]blade7[/TD]
[/TR]
[TR]
[TD]example product C[/TD]
[TD]disc[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]publish[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]visible[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]instock[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]15[/TD]
[TD]Mounted[/TD]
[TD]Mounted[/TD]
[TD][/TD]
[TD]blade10[/TD]
[/TR]
[TR]
[TD]example product C[/TD]
[TD]disc[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]publish[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]visible[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]instock[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]15[/TD]
[TD]Mounted5[/TD]
[TD]Mounted5[/TD]
[TD][/TD]
[TD]blade10[/TD]
[/TR]
[TR]
[TD]example product C[/TD]
[TD]disc[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]publish[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]visible[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]instock[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]15[/TD]
[TD]Mounted9[/TD]
[TD]Mounted9[/TD]
[TD][/TD]
[TD]blade11[/TD]
[/TR]
[TR]
[TD]example product C[/TD]
[TD]disc[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]publish[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]visible[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]instock[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]15[/TD]
[TD]Motor7[/TD]
[TD]Motor7[/TD]
[TD][/TD]
[TD]blade15[/TD]
[/TR]
</tbody>[/TABLE]

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 tax:product_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 tax:product_cat column so that cab be ignored. However I need to merge the tax:product_cat column and retain all values.

So in the example data shown above for example product A the tax:product_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

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]post_title[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]post_excerpt[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]post_status[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]visibility[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]stock_status[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]regular_price[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 268"]
<tbody>[TR]
[TD="width: 268"]tax:product_cat[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]attribute: pa_model[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]attribute: pa_alternative-part-numbers[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]attribute: pa_part-type[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]example product A[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]screw[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]publish[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]visible[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]instock[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: center"]0.18[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Commander|Highway 3|Highway[/TD]
[TD]Commander|Highway 3|Highway[/TD]
[TD][/TD]
[TD]Screw-Bottom[/TD]
[/TR]
[TR]
[TD]example product B[/TD]
[TD]blade[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]publish[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]visible[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]instock[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]20[/TD]
[TD]Parkway|Parkway2[/TD]
[TD]Parkway|Parkway2[/TD]
[TD][/TD]
[TD]blade7[/TD]
[/TR]
[TR]
[TD]example product C[/TD]
[TD]disc[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]publish[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]visible[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]instock[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]15[/TD]
[TD]Mounted|Mounted5|Mounted9|Motor7[/TD]
[TD]Mounted|Mounted5|Mounted9|Motor7[/TD]
[TD][/TD]
[TD]blade10|blade11|blade15[/TD]
[/TR]
</tbody>[/TABLE]

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.
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Sort by date Sort by votes
Try this:-
NB:- This code will update and remove unwanted rows from your Data.
Code: 
[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
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Help with separating Text String from a Excel Col
Replies
3
Views
592
Peter_SSs
Peter_SSs
M
  • Question Question
VBA to format Cells with Borders based on Column Value ( merged cells)
Replies
1
Views
977
DanteAmor
DanteAmor
S
  • Question Question
Merging Cells and retaining data when in a row of merged cells.
Replies
0
Views
176
sumthing
S
DocAElstein
  • Question Question
Simple Data Sort & Merge Code.
Replies
5
Views
23K
DocAElstein
DocAElstein
X
  • Question Question
extracting expesific data from prodoct list
Replies
3
Views
1K
Akashwani
Akashwani

Forum statistics

Threads
1,223,227
Messages
6,170,849
Members
452,361
Latest member
d3ad3y3

Share this page

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