VBA layout transformation

TCookie

New Member
Joined
Jun 16, 2015
Messages
11
Hi there,

Could you tell me how I can transform table 1 layout below to table 2 layout please. Table 1 list similar products in groups by placing them straight after another and use a blank cell(s) to differential from a different group. In order for me to make use of the data, I need to transform the data into table 2 layout, where it shows the products and the alternative products.

Any help will be very much appreciated.


Table 1
Product
JK2309
KN4506
JN3950
VN3890
JK1983
DE39833
DN2928
NJ92
NH12
KO12

<colgroup><col></colgroup><tbody>
</tbody>


Table 2
ProductAlternative Product
JK2309KN4506
JK2309JN3950
KN4506JK2309
KN4506JN3950
JN3950JK2309
JN3950KN4506
VN3890JK1983
VN3890DE39833
VN3890DN2928
VN3890NJ92
JK1983VN3890
JK1983DE39833
JK1983DN2928
JK1983NJ92
DE39833VN3890
DE39833JK1983
DE39833DN2928
DE39833NJ92
DN2928VN3890
DN2928JK1983
DN2928DE39833
DN2928NJ92
NJ92VN3890
NJ92JK1983
NJ92DE39833
NJ92DN2928
NH12KO12
KO12NH12

<colgroup><col><col></colgroup><tbody>
</tbody>
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Try this for results in columns B & C.
Code:
[COLOR="Navy"]Sub[/COLOR] MG01Oct56
im Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, R [COLOR="Navy"]As[/COLOR] Range, RR [COLOR="Navy"]As[/COLOR] Range, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range("A2", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants)
Range("B1:C1").Value = Array("Product", "Alternative Product")
c = 1
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng.Areas
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] R [COLOR="Navy"]In[/COLOR] Dn
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] RR [COLOR="Navy"]In[/COLOR] Dn
            [COLOR="Navy"]If[/COLOR] Not R = RR [COLOR="Navy"]Then[/COLOR]
                c = c + 1
                Cells(c, "B") = R
                Cells(c, "C") = RR
            [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]Next[/COLOR] RR
    [COLOR="Navy"]Next[/COLOR] R
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0

Forum statistics

Threads
1,213,539
Messages
6,114,221
Members
448,554
Latest member
Gleisner2

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