Multiple pasting with transpose

[MASOODAHMAD]

Hi,

I got some work in Excel which is briefed as below:

INPUT:
Company names, web address and Categories*

* Categories means to which single/multiple category the company belongs to. i.e. a company can be part of A1 only or it could be a part of A1, A2, B5, J13 etc. The categories comes in multiple columns.

OUTPUT:
1.The Categories are in Columnar style which needs to be converted in rows.
2. I copy all the Categories of one company and then Transpose/paste them in a different sheet2. i.e. converting columns to rows.
3. I then copy the Company name and Web address and go to sheet 2 and select 2 columns (for Company name and Web) and select all the rows which contains the Categories (I pasted using Transpose) i.e. Categories on the left containing the Category, and finally paste the Company name and Web address.
4. In this way the company name is listed in front of its categories. Sometimes it is single and sometimes it is multiple.
5. I do this for about 5 hours as there are thousands of entries.


SOLUTION:
Is there any better way of doing so. I tried the macros but that only help when you have fixed number of elements. Therefore, I am looking for something else.


Small example of my work is given below:

Version:1.0 StartHTML:0000000177 EndHTML:0000017242 StartFragment:0000007016 EndFragment:0000017196 SourceURL:file://localhost/Users/agi-sony/Desktop/SAMPLE_Transpose.xls ****** http-equiv="Content-Type" content="text/html; charset=utf-8"> ****** name="ProgId" content="Excel.Sheet"> ****** name="Generator" content="Microsoft Excel 11"> <link id="Main-File" rel="Main-File" href="file://localhost/Users/agi-sony/Library/Caches/TemporaryItems/msoclip1/01/clip.htm"> <style> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:11.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} .xl71 {font-weight:700; text-align:center; border-top:none; border-right:none; border-bottom:.5pt solid windowtext; border-left:none; background:#99CC00; mso-pattern:auto none;} .xl72 {font-size:12.0pt; font-weight:700; font-family:Tahoma, sans-serif; mso-font-charset:0; text-align:left; vertical-align:top; border-top:.5pt solid windowtext; border-right:.5pt hairline windowtext; border-bottom:.5pt hairline windowtext; border-left:.5pt solid windowtext; background:#FF9900; mso-pattern:auto none;} .xl73 {font-size:12.0pt; font-weight:700; font-family:Tahoma, sans-serif; mso-font-charset:0; text-align:left; vertical-align:top; border-top:.5pt solid windowtext; border-right:.5pt hairline windowtext; border-bottom:.5pt hairline windowtext; border-left:.5pt hairline windowtext; background:#FF9900; mso-pattern:auto none;} .xl74 {font-size:12.0pt; font-weight:700; font-family:Tahoma, sans-serif; mso-font-charset:0; text-align:center; vertical-align:top; border-top:.5pt solid windowtext; border-right:.5pt hairline windowtext; border-bottom:.5pt hairline windowtext; border-left:.5pt hairline windowtext; background:#FF9900; mso-pattern:auto none;} .xl75 {font-size:12.0pt; font-weight:700; font-family:Tahoma, sans-serif; mso-font-charset:0; text-align:center; vertical-align:top; border-top:.5pt solid windowtext; border-right:.5pt solid windowtext; border-bottom:.5pt hairline windowtext; border-left:.5pt hairline windowtext; background:#FF9900; mso-pattern:auto none;} .xl76 {font-weight:700; font-family:Tahoma, sans-serif; mso-font-charset:0; text-align:left; vertical-align:top; border-top:.5pt hairline windowtext; border-right:.5pt hairline windowtext; border-bottom:.5pt hairline windowtext; border-left:.5pt solid windowtext;} .xl77 {color:#0000D4; text-decoration:underline; text-underline-style:single; text-align:left; vertical-align:top; border:.5pt hairline windowtext;} .xl78 {border:.5pt hairline windowtext;} .xl79 {border-top:.5pt hairline windowtext; border-right:.5pt solid windowtext; border-bottom:.5pt hairline windowtext; border-left:.5pt hairline windowtext;} .xl80 {font-weight:700; font-family:Tahoma, sans-serif; mso-font-charset:0; text-align:left; vertical-align:top; border-top:.5pt hairline windowtext; border-right:.5pt hairline windowtext; border-bottom:.5pt hairline windowtext; border-left:.5pt solid windowtext;} .xl81 {font-weight:700; font-family:Tahoma, sans-serif; mso-font-charset:0; text-align:left; vertical-align:top; border-top:.5pt hairline windowtext; border-right:.5pt hairline windowtext; border-bottom:.5pt solid windowtext; border-left:.5pt solid windowtext;} .xl82 {color:#0000D4; text-decoration:underline; text-underline-style:single; text-align:left; vertical-align:top; border-top:.5pt hairline windowtext; border-right:.5pt hairline windowtext; border-bottom:.5pt solid windowtext; border-left:.5pt hairline windowtext;} .xl83 {border-top:.5pt hairline windowtext; border-right:.5pt hairline windowtext; border-bottom:.5pt solid windowtext; border-left:.5pt hairline windowtext;} .xl84 {border-top:.5pt hairline windowtext; border-right:.5pt solid windowtext; border-bottom:.5pt solid windowtext; border-left:.5pt hairline windowtext;} .xl85 {border-top:.5pt solid windowtext; border-right:.5pt hairline windowtext; border-bottom:.5pt hairline windowtext; border-left:.5pt solid windowtext;} .xl86 {font-weight:700; font-family:Tahoma, sans-serif; mso-font-charset:0; text-align:left; vertical-align:top; border-top:.5pt solid windowtext; border-right:.5pt hairline windowtext; border-bottom:.5pt hairline windowtext; border-left:.5pt hairline windowtext;} .xl87 {color:#0000D4; text-decoration:underline; text-underline-style:single; text-align:left; vertical-align:top; border-top:.5pt solid windowtext; border-right:.5pt hairline windowtext; border-bottom:.5pt hairline windowtext; border-left:.5pt hairline windowtext;} .xl88 {color:#0000D4; text-decoration:underline; text-underline-style:single; text-align:left; vertical-align:top; border-top:.5pt solid windowtext; border-right:.5pt solid windowtext; border-bottom:.5pt hairline windowtext; border-left:.5pt hairline windowtext;} .xl89 {border-top:.5pt hairline windowtext; border-right:.5pt hairline windowtext; border-bottom:.5pt hairline windowtext; border-left:.5pt solid windowtext;} .xl90 {font-weight:700; font-family:Tahoma, sans-serif; mso-font-charset:0; text-align:left; vertical-align:top; border:.5pt hairline windowtext;} .xl91 {color:#0000D4; text-decoration:underline; text-underline-style:single; text-align:left; vertical-align:top; border-top:.5pt hairline windowtext; border-right:.5pt solid windowtext; border-bottom:.5pt hairline windowtext; border-left:.5pt hairline windowtext;} .xl92 {font-weight:700; font-family:Tahoma, sans-serif; mso-font-charset:0; text-align:left; vertical-align:top; border:.5pt hairline windowtext;} .xl93 {border-top:.5pt hairline windowtext; border-right:.5pt hairline windowtext; border-bottom:.5pt solid windowtext; border-left:.5pt solid windowtext;} .xl94 {font-weight:700; font-family:Tahoma, sans-serif; mso-font-charset:0; text-align:left; vertical-align:top; border-top:.5pt hairline windowtext; border-right:.5pt hairline windowtext; border-bottom:.5pt solid windowtext; border-left:.5pt hairline windowtext;} .xl95 {color:#0000D4; text-decoration:underline; text-underline-style:single; text-align:left; vertical-align:top; border-top:.5pt hairline windowtext; border-right:.5pt solid windowtext; border-bottom:.5pt solid windowtext; border-left:.5pt hairline windowtext;} --> </style> <table style="border-collapse: collapse;" border="0" cellpadding="0" cellspacing="0" width="733"> <!--StartFragment--> <col width="264"> <col style="" width="185"> <col style="" width="26"> <col style="" span="5" width="29"> <col style="" span="2" width="28"> <col style="" width="29"> <col style="" width="28"> <tbody><tr height="15"> <td colspan="12" class="xl71" height="15" width="733">INPUT</td> </tr> <tr style="" height="19"> <td class="xl72" height="19">Company Name</td> <td class="xl73">Web</td> <td colspan="10" class="xl74" style="border-right: 0.5pt solid black;">Categories</td> </tr> <tr height="18"> <td class="xl76" height="18">121 Systems</td> <td class="xl77">www.121systems.com</td> <td class="xl78">A13</td> <td class="xl78">F10</td> <td class="xl78"> </td> <td class="xl78"> </td> <td class="xl78"> </td> <td class="xl78"> </td> <td class="xl78"> </td> <td class="xl78"> </td> <td class="xl78"> </td> <td class="xl79"> </td> </tr> <tr height="18"> <td class="xl76" height="18">3 Sun Renewables</td> <td class="xl77">www.3sun.co.uk</td> <td class="xl78">A17</td> <td class="xl78">B01</td> <td class="xl78">B13</td> <td class="xl78">K10</td> <td class="xl78">J12</td> <td class="xl78">J13</td> <td class="xl78">C02</td> <td class="xl78">K22</td> <td class="xl78">C03</td> <td class="xl79">C07</td> </tr> <tr height="15"> <td class="xl76" height="15">3d Web Technologies Ltd</td> <td class="xl77">www.3dwebtech.co.uk</td> <td class="xl78">B01</td> <td class="xl78"> </td> <td class="xl78"> </td> <td class="xl78"> </td> <td class="xl78"> </td> <td class="xl78"> </td> <td class="xl78"> </td> <td class="xl78"> </td> <td class="xl78"> </td> <td class="xl79"> </td> </tr> <tr height="18"> <td class="xl76" height="18">3E</td> <td class="xl77">www.3E.eu</td> <td class="xl78">A05</td> <td class="xl78">A15</td> <td class="xl78">A18</td> <td class="xl78">A02</td> <td class="xl78">A11</td> <td class="xl78">I03</td> <td class="xl78">B11</td> <td class="xl78">K22</td> <td class="xl78">C07</td> <td class="xl79">E03</td> </tr> <tr height="15"> <td class="xl76" height="15">4 Projects</td> <td class="xl77">www.4projects.com</td> <td class="xl78">A13</td> <td class="xl78"> </td> <td class="xl78"> </td> <td class="xl78"> </td> <td class="xl78"> </td> <td class="xl78"> </td> <td class="xl78"> </td> <td class="xl78"> </td> <td class="xl78"> </td> <td class="xl79"> </td> </tr> <tr height="18"> <td class="xl80" height="18">ABB Limited</td> <td class="xl77">www.abb.com</td> <td class="xl78">J13</td> <td class="xl78">E09</td> <td class="xl78">C03</td> <td class="xl78">D02</td> <td class="xl78">E07</td> <td class="xl78">F10</td> <td class="xl78">E08</td> <td class="xl78">E04</td> <td class="xl78"> </td> <td class="xl79"> </td> </tr> <tr height="18"> <td class="xl81" height="18">Abermed Limited</td> <td class="xl82">www.abermed.com </td> <td class="xl83">A10</td> <td class="xl83">I04</td> <td class="xl83"> </td> <td class="xl83"> </td> <td class="xl83"> </td> <td class="xl83"> </td> <td class="xl83"> </td> <td class="xl83"> </td> <td class="xl83"> </td> <td class="xl84"> </td> </tr> <tr height="15"> <td height="15"></td> <td></td> <td></td> <td></td> <td></td> <td></td> <td></td> <td></td> <td></td> <td></td> <td></td> <td></td> </tr> <tr height="15"> <td height="15"></td> <td></td> <td></td> <td></td> <td></td> <td></td> <td></td> <td></td> <td></td> <td></td> <td></td> <td></td> </tr> <tr height="15"> <td colspan="12" class="xl71" height="15">OUTPUT</td> </tr> <tr height="15"> <td class="xl85" height="15">A13</td> <td class="xl86">121 Systems</td> <td colspan="10" class="xl87" style="border-right: 0.5pt solid black;">www.121systems.com</td> </tr> <tr height="15"> <td class="xl89" height="15">F10</td> <td class="xl90">121 Systems</td> <td colspan="10" class="xl77" style="border-right: 0.5pt solid black;">www.121systems.com</td> </tr> <tr height="15"> <td class="xl89" height="15">A17</td> <td class="xl90">3 Sun Renewables</td> <td colspan="10" class="xl77" style="border-right: 0.5pt solid black;">www.3sun.co.uk</td> </tr> <tr height="15"> <td class="xl89" height="15">B01</td> <td class="xl90">3 Sun Renewables</td> <td colspan="10" class="xl77" style="border-right: 0.5pt solid black;">www.3sun.co.uk</td> </tr> <tr height="15"> <td class="xl89" height="15">B13</td> <td class="xl90">3 Sun Renewables</td> <td colspan="10" class="xl77" style="border-right: 0.5pt solid black;">www.3sun.co.uk</td> </tr> <tr height="15"> <td class="xl89" height="15">K10</td> <td class="xl90">3 Sun Renewables</td> <td colspan="10" class="xl77" style="border-right: 0.5pt solid black;">www.3sun.co.uk</td> </tr> <tr height="15"> <td class="xl89" height="15">J12</td> <td class="xl90">3 Sun Renewables</td> <td colspan="10" class="xl77" style="border-right: 0.5pt solid black;">www.3sun.co.uk</td> </tr> <tr height="15"> <td class="xl89" height="15">J13</td> <td class="xl90">3 Sun Renewables</td> <td colspan="10" class="xl77" style="border-right: 0.5pt solid black;">www.3sun.co.uk</td> </tr> <tr height="15"> <td class="xl89" height="15">C02</td> <td class="xl90">3 Sun Renewables</td> <td colspan="10" class="xl77" style="border-right: 0.5pt solid black;">www.3sun.co.uk</td> </tr> <tr height="15"> <td class="xl89" height="15">K22</td> <td class="xl90">3 Sun Renewables</td> <td colspan="10" class="xl77" style="border-right: 0.5pt solid black;">www.3sun.co.uk</td> </tr> <tr height="15"> <td class="xl89" height="15">C03</td> <td class="xl90">3 Sun Renewables</td> <td colspan="10" class="xl77" style="border-right: 0.5pt solid black;">www.3sun.co.uk</td> </tr> <tr height="15"> <td class="xl89" height="15">C07</td> <td class="xl90">3 Sun Renewables</td> <td colspan="10" class="xl77" style="border-right: 0.5pt solid black;">www.3sun.co.uk</td> </tr> <tr height="15"> <td class="xl89" height="15">B01</td> <td class="xl90">3d Web Technologies Ltd</td> <td colspan="10" class="xl77" style="border-right: 0.5pt solid black;">www.3dwebtech.co.uk</td> </tr> <tr height="15"> <td class="xl89" height="15">A05</td> <td class="xl90">3E</td> <td colspan="10" class="xl77" style="border-right: 0.5pt solid black;">www.3E.eu</td> </tr> <tr height="15"> <td class="xl89" height="15">A15</td> <td class="xl90">3E</td> <td colspan="10" class="xl77" style="border-right: 0.5pt solid black;">www.3E.eu</td> </tr> <tr height="15"> <td class="xl89" height="15">A18</td> <td class="xl90">3E</td> <td colspan="10" class="xl77" style="border-right: 0.5pt solid black;">www.3E.eu</td> </tr> <tr height="15"> <td class="xl89" height="15">A02</td> <td class="xl90">3E</td> <td colspan="10" class="xl77" style="border-right: 0.5pt solid black;">www.3E.eu</td> </tr> <tr height="15"> <td class="xl89" height="15">A11</td> <td class="xl90">3E</td> <td colspan="10" class="xl77" style="border-right: 0.5pt solid black;">www.3E.eu</td> </tr> <tr height="15"> <td class="xl89" height="15">I03</td> <td class="xl90">3E</td> <td colspan="10" class="xl77" style="border-right: 0.5pt solid black;">www.3E.eu</td> </tr> <tr height="15"> <td class="xl89" height="15">B11</td> <td class="xl90">3E</td> <td colspan="10" class="xl77" style="border-right: 0.5pt solid black;">www.3E.eu</td> </tr> <tr height="15"> <td class="xl89" height="15">K22</td> <td class="xl90">3E</td> <td colspan="10" class="xl77" style="border-right: 0.5pt solid black;">www.3E.eu</td> </tr> <tr height="15"> <td class="xl89" height="15">C07</td> <td class="xl90">3E</td> <td colspan="10" class="xl77" style="border-right: 0.5pt solid black;">www.3E.eu</td> </tr> <tr height="15"> <td class="xl89" height="15">E03</td> <td class="xl90">3E</td> <td colspan="10" class="xl77" style="border-right: 0.5pt solid black;">www.3E.eu</td> </tr> <tr height="15"> <td class="xl89" height="15">A13</td> <td class="xl90">4 Projects</td> <td colspan="10" class="xl77" style="border-right: 0.5pt solid black;">www.4projects.com</td> </tr> <tr height="15"> <td class="xl89" height="15">J13</td> <td class="xl92">ABB Limited</td> <td colspan="10" class="xl77" style="border-right: 0.5pt solid black;">www.abb.com</td> </tr> <tr height="15"> <td class="xl89" height="15">E09</td> <td class="xl92">ABB Limited</td> <td colspan="10" class="xl77" style="border-right: 0.5pt solid black;">www.abb.com</td> </tr> <tr height="15"> <td class="xl89" height="15">C03</td> <td class="xl92">ABB Limited</td> <td colspan="10" class="xl77" style="border-right: 0.5pt solid black;">www.abb.com</td> </tr> <tr height="15"> <td class="xl89" height="15">D02</td> <td class="xl92">ABB Limited</td> <td colspan="10" class="xl77" style="border-right: 0.5pt solid black;">www.abb.com</td> </tr> <tr height="15"> <td class="xl89" height="15">E07</td> <td class="xl92">ABB Limited</td> <td colspan="10" class="xl77" style="border-right: 0.5pt solid black;">www.abb.com</td> </tr> <tr height="15"> <td class="xl89" height="15">F10</td> <td class="xl92">ABB Limited</td> <td colspan="10" class="xl77" style="border-right: 0.5pt solid black;">www.abb.com</td> </tr> <tr height="15"> <td class="xl89" height="15">E08</td> <td class="xl92">ABB Limited</td> <td colspan="10" class="xl77" style="border-right: 0.5pt solid black;">www.abb.com</td> </tr> <tr height="15"> <td class="xl89" height="15">E04</td> <td class="xl92">ABB Limited</td> <td colspan="10" class="xl77" style="border-right: 0.5pt solid black;">www.abb.com</td> </tr> <tr height="15"> <td class="xl89" height="15">A10</td> <td class="xl90">Abermed Limited</td> <td colspan="10" class="xl77" style="border-right: 0.5pt solid black;">www.abermed.com </td> </tr> <tr height="15"> <td class="xl93" height="15">I04</td> <td class="xl94">Abermed Limited</td> <td colspan="10" class="xl82" style="border-right: 0.5pt solid black;">www.abermed.com </td> </tr> <!--EndFragment--> </tbody></table>

Looking for something great from you guys.

Thanks,

Masood Ahmad

 

[MickG]

Try this:-
Results sheet(2)

[COLOR="Navy"]Sub[/COLOR] MG28Mar37
[COLOR="Navy"]Dim[/COLOR] Rng     [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dn      [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] AcRng   [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Ac      [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] c       [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
    ReDim Ray(1 To Rng.Count * 10, 1 To 3)
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
  [COLOR="Navy"]Set[/COLOR] AcRng = Range(Cells(Dn.Row, 3), Cells(Dn.Row, Columns.Count).End(xlToLeft))
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Ac [COLOR="Navy"]In[/COLOR] AcRng
        c = c + 1
        Ray(c, 1) = Ac: Ray(c, 2) = Dn: Ray(c, 3) = Dn.Offset(, 1)
    [COLOR="Navy"]Next[/COLOR] Ac
[COLOR="Navy"]Next[/COLOR] Dn
    [COLOR="Navy"]With[/COLOR] Sheets("Sheet2")
        .Range("A2").Resize(c, 3).Value = Ray
        .Columns("A:C").AutoFit
    [COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[MASOODAHMAD]

Hi Mick,

It's fabulous. I don't have words to thank you. It's remarkable, undoubtedly the best solution I have ever received. Very quick of course.

Just going greedy, please send me your email address on masoodahmad@inbox.com. I will keep it handy in case I need something urgent.

One more thing, is there any way to convert VB scripts into Add-Ins.

Once again I pay my gratitude for your valuable and effective timely solution. I salute you Sir. I am amazed. I use to spend 4-5 hours on this project and now it's a 30 minute job including other things.


Thanks a lot!!!!!

Masood Ahmad
ray:

 

[MASOODAHMAD]

Hi Mick,

It's fabulous. I don't have words to thank you. It's remarkable, undoubtedly the best solution I have ever received. Very quick of course.

Just going greedy, please send me your email address on masoodahmad@inbox.com. I will keep it handy in case I need something urgent.

One more thing, is there any way to convert VB scripts into Add-Ins.

Once again I pay my gratitude for your valuable and effective timely solution. I salute you Sir. I am amazed. I use to spend 4-5 hours on this project and now it's a 30 minute job including other things.


Thanks a lot!!!!!

Masood Ahmad
ray:

 

[MickG]

If you want to use the code throughout various workbooks/Sheets it would be easier to place it in a "Personal Macro Workbook".