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>
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
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
 

TCookie

New Member
Joined
Jun 16, 2015
Messages
11
Oh whoa....this is amazing. Thank you so much for this. :biggrin: This is exactly what I needed.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,532
Messages
5,529,393
Members
409,870
Latest member
Well59
Top