VBA - Rearrange data from Horizontal to Vertical

iris1007

New Member
Joined
Jun 28, 2017
Messages
31
Hi
I have a huge data (sample as below), which i need to rearrange it from Horizontal to Vertical.

Before :
OPERATORJan-08Feb-08Mar-08Apr-08May-08Jun-08Jul-08Aug-08Sep-08Oct-08Nov-08Dec-08
JAPAN303.13289.10337.18290.15318.40284.58334.58294.18288.12283.98302.33309.93
JAPAN267.85307.88312.80294.47309.87295.38279.77277.17167.4746.27265.33242.38
JAPAN320.85304.37328.97314.33307.27317.38286.85310.03314.27296.70331.98361.90
JAPAN318.57323.72290.60291.73306.05294.13265.57267.93322.32287.35314.58
JAPAN311.63310.90329.83259.70308.27299.18303.17326.27318.23352.43
JAPAN262.43320.20326.95307.50296.28332.87314.65331.10207.705.28279.22
USA274.90176.32331.73303.28318.12303.30333.17294.98333.53293.15333.90
USA329.05302.17284.77291.92322.68320.77331.12278.28304.47334.02
USA331.80307.90352.58269.82324.35305.67331.45298.48218.87311.10312.47345.27
USA301.60316.45316.08304.48311.25305.15294.13243.77321.13318.80318.82336.32
USA331.83316.53341.35314.22312.05267.68351.55262.00277.18326.50310.53358.68
HONG KONG309.97314.40318.02313.60294.65322.98309.70315.45285.93297.70
HONG KONG301.88297.65317.13207.45266.95312.82330.45298.05346.65269.58317.13313.68
HONG KONG312.03337.80322.90298.55320.35284.42302.30302.45323.38322.53329.95
HONG KONG324.67307.60330.67312.85327.15277.22330.33311.83279.12335.33305.27333.97
HONG KONG315.08293.63307.48324.38338.67154.25259.60111.57245.355.32247.60
HONG KONG317.97303.70315.78313.12319.88321.30319.48271.13290.58279.00296.82302.30
INDIA323.38291.62240.45258.85284.20274.15305.40317.95329.28344.37309.60321.92
INDIA294.77310.02340.15313.97315.72315.00309.55326.80317.65307.60308.75
INDIA322.45282.17306.07310.47320.37315.50337.98294.53294.27271.88310.65315.17

<tbody>
</tbody>



I would like to rearrange it to...
After:
OPERATORVolumeMONTH
JAPAN303.1333333Jan-04
JAPAN267.85Jan-04
JAPAN320.85Jan-04
JAPAN318.5666667Jan-04
JAPAN311.6333333Jan-04
JAPAN262.4333333Jan-04
AIR ASIA - MAA232.6166667Jan-04
USA274.9Jan-04
USA329.05Jan-04
USA331.8Jan-04
AIR ASIA - MAA303.9333333Jan-04
AIR ASIA - MAA334.5166667Jan-04
AIR ASIA - MAA324.1166667Jan-04
USA301.6Jan-04
USA331.8333333Jan-04
AIR ASIA - MAA324.1166667Jan-04
AIR ASIA - MAA332.3833333Jan-04
HONG KONG301.8833333Jan-04
HONG KONG312.0333333Jan-04
HONG KONG324.6666667Jan-04
HONG KONG315.0833333Jan-04
HONG KONG317.9666667Jan-04
INDIA323.3833333Jan-04
INDIA294.7666667Jan-04
INDIA322.45Jan-04
JAPAN289.1Feb-04
JAPAN307.8833333Feb-04
JAPAN304.3666667Feb-04
JAPAN323.7166667Feb-04
JAPAN310.9Feb-04
JAPAN320.2Feb-04
AIR ASIA - MAA305.9333333Feb-04
USA176.3166667Feb-04
USA302.1666667Feb-04
USA307.9Feb-04
AIR ASIA - MAA275.2833333Feb-04
AIR ASIA - MAA310.0166667Feb-04
AIR ASIA - MAA291.15Feb-04
USA316.45Feb-04
USA316.5333333Feb-04
AIR ASIA - MAA322.5666667Feb-04
AIR ASIA - MAA324.6833333Feb-04
HONG KONG309.9666667Feb-04
HONG KONG297.65Feb-04
HONG KONG307.6Feb-04
HONG KONG293.6333333Feb-04
HONG KONG303.7Feb-04
INDIA291.6166667Feb-04
INDIA310.0166667Feb-04
INDIA282.1666667Feb-04
JAPAN337.1833333Mar-04
JAPAN312.8Mar-04
JAPAN328.9666667Mar-04
JAPAN290.6Mar-04
JAPAN329.8333333Mar-04
JAPAN326.95Mar-04
AIR ASIA - MAA311.1666667Mar-04
USA331.7333333Mar-04
USA284.7666667Mar-04
USA352.5833333Mar-04
AIR ASIA - MAA312.8Mar-04
AIR ASIA - MAA338.3833333Mar-04
AIR ASIA - MAA347.35Mar-04
USA316.0833333Mar-04
USA341.35Mar-04
AIR ASIA - MAA319.6666667Mar-04
AIR ASIA - MAA339.3666667Mar-04
HONG KONG314.4Mar-04
HONG KONG317.1333333Mar-04
HONG KONG337.8Mar-04
HONG KONG330.6666667Mar-04
HONG KONG307.4833333Mar-04
HONG KONG315.7833333Mar-04
INDIA240.45Mar-04
INDIA340.15Mar-04
INDIA306.0666667Mar-04
JAPAN290.15Apr-04
JAPAN294.4666667Apr-04
JAPAN314.3333333Apr-04
JAPAN291.7333333Apr-04
JAPAN259.7Apr-04
JAPAN307.5Apr-04
AIR ASIA - MAA306.8333333Apr-04
USA303.2833333Apr-04
USA291.9166667Apr-04
USA269.8166667Apr-04
AIR ASIA - MAA310.65Apr-04
AIR ASIA - MAA321Apr-04
AIR ASIA - MAA315.4833333Apr-04
USA304.4833333Apr-04
USA314.2166667Apr-04
AIR ASIA - MAA292.3666667Apr-04
AIR ASIA - MAA329.9166667Apr-04
HONG KONG318.0166667Apr-04
HONG KONG207.45Apr-04
HONG KONG322.9Apr-04
HONG KONG312.85Apr-04
HONG KONG324.3833333Apr-04
HONG KONG313.1166667Apr-04
INDIA258.85Apr-04
INDIA313.9666667Apr-04
INDIA310.4666667Apr-04
JAPAN318.4May-04
JAPAN309.8666667May-04
JAPAN307.2666667May-04
JAPAN306.05May-04
JAPAN308.2666667May-04
USA324.35May-04
AIR ASIA - MAA270.3666667May-04
AIR ASIA - MAA296.9666667May-04
AIR ASIA - MAA317.9166667May-04
USA311.25May-04
USA312.05May-04
AIR ASIA - MAA346.3166667May-04
AIR ASIA - MAA325.2333333May-04
HONG KONG313.6May-04
HONG KONG266.95May-04
HONG KONG298.55May-04
HONG KONG327.15May-04
HONG KONG338.6666667May-04
HONG KONG319.8833333May-04
INDIA284.2May-04
INDIA315.7166667May-04
INDIA320.3666667May-04
JAPAN284.5833333Jun-04
JAPAN295.3833333Jun-04
JAPAN317.3833333Jun-04
JAPAN294.1333333Jun-04
JAPAN299.1833333Jun-04
JAPAN296.2833333Jun-04
AIR ASIA - MAA283.6166667Jun-04
USA318.1166667Jun-04
USA322.6833333Jun-04
USA305.6666667Jun-04
AIR ASIA - MAA303.5666667Jun-04
AIR ASIA - MAA279.5666667Jun-04
AIR ASIA - MAA242.9Jun-04
USA305.15Jun-04
USA267.6833333Jun-04
AIR ASIA - MAA308.7666667Jun-04
AIR ASIA - MAA308.85Jun-04
HONG KONG294.65Jun-04
HONG KONG312.8166667Jun-04
HONG KONG320.35Jun-04
HONG KONG277.2166667Jun-04
HONG KONG154.25Jun-04
HONG KONG321.3Jun-04
INDIA274.15Jun-04
INDIA315.5Jun-04
JAPAN334.5833333Jul-04
JAPAN279.7666667Jul-04
JAPAN286.85Jul-04
JAPAN303.1666667Jul-04
JAPAN332.8666667Jul-04
AIR ASIA - MAA338.25Jul-04
USA303.3Jul-04
USA320.7666667Jul-04
USA331.45Jul-04
AIR ASIA - MAA277.3833333Jul-04
AIR ASIA - MAA314.4Jul-04
AIR ASIA - MAA329.9166667Jul-04
USA294.1333333Jul-04
USA351.55Jul-04
AIR ASIA - MAA339.85Jul-04
AIR ASIA - MAA212.4833333Jul-04
HONG KONG322.9833333Jul-04
HONG KONG330.45Jul-04
HONG KONG284.4166667Jul-04
HONG KONG330.3333333Jul-04
HONG KONG319.4833333Jul-04
INDIA305.4Jul-04
INDIA315Jul-04
INDIA337.9833333Jul-04
JAPAN294.1833333Aug-04
JAPAN277.1666667Aug-04
JAPAN310.0333333Aug-04
JAPAN265.5666667Aug-04
JAPAN326.2666667Aug-04
JAPAN314.65Aug-04
AIR ASIA - MAA305.1166667Aug-04
USA333.1666667Aug-04
USA331.1166667Aug-04
USA298.4833333Aug-04
AIR ASIA - MAA279.1833333Aug-04
AIR ASIA - MAA292.2166667Aug-04
AIR ASIA - MAA321.05Aug-04
USA243.7666667Aug-04
USA262Aug-04
AIR ASIA - MAA239.2166667Aug-04
AIR ASIA - MAA338.4833333Aug-04
HONG KONG309.7Aug-04
HONG KONG298.05Aug-04
HONG KONG302.3Aug-04
HONG KONG311.8333333Aug-04
HONG KONG259.6Aug-04
HONG KONG271.1333333Aug-04
INDIA317.95Aug-04
INDIA309.55Aug-04
INDIA294.5333333Aug-04
JAPAN288.1166667Sep-04
JAPAN167.4666667Sep-04
JAPAN314.2666667Sep-04
JAPAN267.9333333Sep-04
JAPAN331.1Sep-04
AIR ASIA - MAA338.95Sep-04
USA294.9833333Sep-04
USA278.2833333Sep-04
USA218.8666667Sep-04
AIR ASIA - MAA304.0333333Sep-04
AIR ASIA - MAA303.4833333Sep-04
AIR ASIA - MAA285.1666667Sep-04
USA321.1333333Sep-04
USA277.1833333Sep-04
AIR ASIA - MAA286.1Sep-04
AIR ASIA - MAA258.7166667Sep-04
HONG KONG315.45Sep-04
HONG KONG346.65Sep-04
HONG KONG302.45Sep-04
HONG KONG279.1166667Sep-04
HONG KONG111.5666667Sep-04
HONG KONG290.5833333Sep-04
INDIA329.2833333Sep-04
INDIA326.8Sep-04
INDIA294.2666667Sep-04
JAPAN283.9833333Oct-04
JAPAN46.26666667Oct-04
JAPAN296.7Oct-04
JAPAN322.3166667Oct-04
JAPAN207.7Oct-04
AIR ASIA - MAA319.75Oct-04
USA333.5333333Oct-04
USA304.4666667Oct-04
USA311.1Oct-04
AIR ASIA - MAA314.1Oct-04
AIR ASIA - MAA321.6833333Oct-04
AIR ASIA - MAA278Oct-04
USA318.8Oct-04
USA326.5Oct-04
AIR ASIA - MAA350.2666667Oct-04
AIR ASIA - MAA297.0333333Oct-04
HONG KONG269.5833333Oct-04
HONG KONG323.3833333Oct-04
HONG KONG335.3333333Oct-04
HONG KONG245.35Oct-04
HONG KONG279Oct-04
INDIA344.3666667Oct-04
INDIA317.65Oct-04
INDIA271.8833333Oct-04
JAPAN302.3333333Nov-04
JAPAN265.3333333Nov-04
JAPAN331.9833333Nov-04
JAPAN287.35Nov-04
JAPAN318.2333333Nov-04
JAPAN5.283333333Nov-04
AIR ASIA - MAA327.7Nov-04
USA293.15Nov-04
USA312.4666667Nov-04
AIR ASIA - MAA313.2666667Nov-04
AIR ASIA - MAA286.1166667Nov-04
AIR ASIA - MAA326.2166667Nov-04
USA318.8166667Nov-04
USA310.5333333Nov-04
AIR ASIA - MAA304.8666667Nov-04
AIR ASIA - MAA268.9Nov-04
HONG KONG285.9333333Nov-04
HONG KONG317.1333333Nov-04
HONG KONG322.5333333Nov-04
HONG KONG305.2666667Nov-04
HONG KONG5.316666667Nov-04
HONG KONG296.8166667Nov-04
INDIA309.6Nov-04
INDIA307.6Nov-04
INDIA310.65Nov-04
JAPAN309.9333333Dec-04
JAPAN242.3833333Dec-04
JAPAN361.9Dec-04
JAPAN314.5833333Dec-04
JAPAN352.4333333Dec-04
JAPAN279.2166667Dec-04
AIR ASIA - MAA345.5Dec-04
USA333.9Dec-04
USA334.0166667Dec-04
USA345.2666667Dec-04
AIR ASIA - MAA313.7833333Dec-04
AIR ASIA - MAA324.8166667Dec-04
AIR ASIA - MAA349.95Dec-04
USA336.3166667Dec-04
USA358.6833333Dec-04
AIR ASIA - MAA362.2Dec-04
AIR ASIA - MAA336.9Dec-04
HONG KONG297.7Dec-04
HONG KONG313.6833333Dec-04
HONG KONG329.95Dec-04
HONG KONG333.9666667Dec-04
HONG KONG247.6Dec-04
HONG KONG302.3Dec-04
INDIA321.9166667Dec-04
INDIA308.75Dec-04
INDIA315.1666667Dec-04

<tbody>
</tbody>


Hope any of you can help and hope to hear the good news soon.
Thanks in advance for all the helps.
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Why is it in your Before image you show no "AIR ASIA"

But in your after image you show "AIR ASIA"
 
Upvote 0
Hi,
My apology. Attached a wrong data.

My before and after data should be as follow:
Before:
OPERATORJan-08Feb-08Mar-08Apr-08May-08Jun-08Jul-08Aug-08Sep-08Oct-08Nov-08Dec-08
JAPAN303.13289.1337.18290.15318.4284.58334.58294.18288.12283.98302.33309.93
JAPAN267.85307.88312.8294.47309.87295.38279.77277.17167.4746.27265.33242.38
JAPAN320.85304.37328.97314.33307.27317.38286.85310.03314.27296.7331.98361.9
JAPAN318.57323.72290.6291.73306.05294.13 265.57267.93322.32287.35314.58
JAPAN311.63310.9329.83259.7308.27299.18303.17326.27 318.23352.43
JAPAN262.43320.2326.95307.5 296.28332.87314.65331.1207.75.28279.22
USA274.9176.32331.73303.28 318.12303.3333.17294.98333.53293.15333.9
USA329.05302.17284.77291.92 322.68320.77331.12278.28304.47 334.02
USA331.8307.9352.58269.82324.35305.67331.45298.48218.87311.1312.47345.27
USA301.6316.45316.08304.48311.25305.15294.13243.77321.13318.8318.82336.32
USA331.83316.53341.35314.22312.05267.68351.55262277.18326.5310.53358.68
HONG KONG 309.97314.4318.02313.6294.65322.98309.7315.45 285.93297.7
HONG KONG301.88297.65317.13207.45266.95312.82330.45298.05346.65269.58317.13313.68
HONG KONG312.03 337.8322.9298.55320.35284.42302.3302.45323.38322.53329.95
HONG KONG324.67307.6330.67312.85327.15277.22330.33311.83279.12335.33305.27333.97
HONG KONG315.08293.63307.48324.38338.67154.25 259.6111.57245.355.32247.6
HONG KONG317.97303.7315.78313.12319.88321.3319.48271.13290.58279296.82302.3
INDIA323.38291.62240.45258.85284.2274.15305.4317.95329.28344.37309.6321.92
INDIA294.77310.02340.15313.97315.72 315309.55326.8317.65307.6308.75
INDIA322.45282.17306.07310.47320.37315.5337.98294.53294.27271.88310.65315.17

<tbody>
</tbody><colgroup><col><col><col><col><col><col><col><col><col><col><col><col span="2"></colgroup>

After:
OPERATORVolumeMONTH
JAPAN303.1333333Jan-04
JAPAN267.85Jan-04
JAPAN320.85Jan-04
JAPAN318.5666667Jan-04
JAPAN311.6333333Jan-04
JAPAN262.4333333Jan-04
USA274.9Jan-04
USA329.05Jan-04
USA331.8Jan-04
USA301.6Jan-04
USA331.8333333Jan-04
HONG KONG301.8833333Jan-04
HONG KONG312.0333333Jan-04
HONG KONG324.6666667Jan-04
HONG KONG315.0833333Jan-04
HONG KONG317.9666667Jan-04
INDIA323.3833333Jan-04
INDIA294.7666667Jan-04
INDIA322.45Jan-04
JAPAN289.1Feb-04
JAPAN307.8833333Feb-04
JAPAN304.3666667Feb-04
JAPAN323.7166667Feb-04
JAPAN310.9Feb-04
JAPAN320.2Feb-04
USA176.3166667Feb-04
USA302.1666667Feb-04
USA307.9Feb-04
USA316.45Feb-04
USA316.5333333Feb-04
HONG KONG309.9666667Feb-04
HONG KONG297.65Feb-04
HONG KONG307.6Feb-04
HONG KONG293.6333333Feb-04
HONG KONG303.7Feb-04
INDIA291.6166667Feb-04
INDIA310.0166667Feb-04
INDIA282.1666667Feb-04
JAPAN337.1833333Mar-04
JAPAN312.8Mar-04
JAPAN328.9666667Mar-04
JAPAN290.6Mar-04
JAPAN329.8333333Mar-04
JAPAN326.95Mar-04
USA331.7333333Mar-04
USA284.7666667Mar-04
USA352.5833333Mar-04
USA316.0833333Mar-04
USA341.35Mar-04
HONG KONG314.4Mar-04
HONG KONG317.1333333Mar-04
HONG KONG337.8Mar-04
HONG KONG330.6666667Mar-04
HONG KONG307.4833333Mar-04
HONG KONG315.7833333Mar-04
INDIA240.45Mar-04
INDIA340.15Mar-04
INDIA306.0666667Mar-04
JAPAN290.15Apr-04
JAPAN294.4666667Apr-04
JAPAN314.3333333Apr-04
JAPAN291.7333333Apr-04
JAPAN259.7Apr-04
JAPAN307.5Apr-04
USA303.2833333Apr-04
USA291.9166667Apr-04
USA269.8166667Apr-04
USA304.4833333Apr-04
USA314.2166667Apr-04
HONG KONG318.0166667Apr-04
HONG KONG207.45Apr-04
HONG KONG322.9Apr-04
HONG KONG312.85Apr-04
HONG KONG324.3833333Apr-04
HONG KONG313.1166667Apr-04
INDIA258.85Apr-04
INDIA313.9666667Apr-04
INDIA310.4666667Apr-04
JAPAN318.4May-04
JAPAN309.8666667May-04
JAPAN307.2666667May-04
JAPAN306.05May-04
JAPAN308.2666667May-04
USA324.35May-04
USA311.25May-04
USA312.05May-04
HONG KONG313.6May-04
HONG KONG266.95May-04
HONG KONG298.55May-04
HONG KONG327.15May-04
HONG KONG338.6666667May-04
HONG KONG319.8833333May-04
INDIA284.2May-04
INDIA315.7166667May-04
INDIA320.3666667May-04
JAPAN284.5833333Jun-04
JAPAN295.3833333Jun-04
JAPAN317.3833333Jun-04
JAPAN294.1333333Jun-04
JAPAN299.1833333Jun-04
JAPAN296.2833333Jun-04
USA318.1166667Jun-04
USA322.6833333Jun-04
USA305.6666667Jun-04
USA305.15Jun-04
USA267.6833333Jun-04
HONG KONG294.65Jun-04
HONG KONG312.8166667Jun-04
HONG KONG320.35Jun-04
HONG KONG277.2166667Jun-04
HONG KONG154.25Jun-04
HONG KONG321.3Jun-04
INDIA274.15Jun-04
INDIA315.5Jun-04
JAPAN334.5833333Jul-04
JAPAN279.7666667Jul-04
JAPAN286.85Jul-04
JAPAN303.1666667Jul-04
JAPAN332.8666667Jul-04
USA303.3Jul-04
USA320.7666667Jul-04
USA331.45Jul-04
USA294.1333333Jul-04
USA351.55Jul-04
HONG KONG322.9833333Jul-04
HONG KONG330.45Jul-04
HONG KONG284.4166667Jul-04
HONG KONG330.3333333Jul-04
HONG KONG319.4833333Jul-04
INDIA305.4Jul-04
INDIA315Jul-04
INDIA337.9833333Jul-04
JAPAN294.1833333Aug-04
JAPAN277.1666667Aug-04
JAPAN310.0333333Aug-04
JAPAN265.5666667Aug-04
JAPAN326.2666667Aug-04
JAPAN314.65Aug-04
USA333.1666667Aug-04
USA331.1166667Aug-04
USA298.4833333Aug-04
USA243.7666667Aug-04
USA262Aug-04
HONG KONG309.7Aug-04
HONG KONG298.05Aug-04
HONG KONG302.3Aug-04
HONG KONG311.8333333Aug-04
HONG KONG259.6Aug-04
HONG KONG271.1333333Aug-04
INDIA317.95Aug-04
INDIA309.55Aug-04
INDIA294.5333333Aug-04
JAPAN288.1166667Sep-04
JAPAN167.4666667Sep-04
JAPAN314.2666667Sep-04
JAPAN267.9333333Sep-04
JAPAN331.1Sep-04
USA294.9833333Sep-04
USA278.2833333Sep-04
USA218.8666667Sep-04
USA321.1333333Sep-04
USA277.1833333Sep-04
HONG KONG315.45Sep-04
HONG KONG346.65Sep-04
HONG KONG302.45Sep-04
HONG KONG279.1166667Sep-04
HONG KONG111.5666667Sep-04
HONG KONG290.5833333Sep-04
INDIA329.2833333Sep-04
INDIA326.8Sep-04
INDIA294.2666667Sep-04
JAPAN283.9833333Oct-04
JAPAN46.26666667Oct-04
JAPAN296.7Oct-04
JAPAN322.3166667Oct-04
JAPAN207.7Oct-04
USA333.5333333Oct-04
USA304.4666667Oct-04
USA311.1Oct-04
USA318.8Oct-04
USA326.5Oct-04
HONG KONG269.5833333Oct-04
HONG KONG323.3833333Oct-04
HONG KONG335.3333333Oct-04
HONG KONG245.35Oct-04
HONG KONG279Oct-04
INDIA344.3666667Oct-04
INDIA317.65Oct-04
INDIA271.8833333Oct-04
JAPAN302.3333333Nov-04
JAPAN265.3333333Nov-04
JAPAN331.9833333Nov-04
JAPAN287.35Nov-04
JAPAN318.2333333Nov-04
JAPAN5.283333333Nov-04
USA293.15Nov-04
USA312.4666667Nov-04
USA318.8166667Nov-04
USA310.5333333Nov-04
HONG KONG285.9333333Nov-04
HONG KONG317.1333333Nov-04
HONG KONG322.5333333Nov-04
HONG KONG305.2666667Nov-04
HONG KONG5.316666667Nov-04
HONG KONG296.8166667Nov-04
INDIA309.6Nov-04
INDIA307.6Nov-04
INDIA310.65Nov-04
JAPAN309.9333333Dec-04
JAPAN242.3833333Dec-04
JAPAN361.9Dec-04
JAPAN314.5833333Dec-04
JAPAN352.4333333Dec-04
JAPAN279.2166667Dec-04
USA333.9Dec-04
USA334.0166667Dec-04
USA345.2666667Dec-04
USA336.3166667Dec-04
USA358.6833333Dec-04
HONG KONG297.7Dec-04
HONG KONG313.6833333Dec-04
HONG KONG329.95Dec-04
HONG KONG333.9666667Dec-04
HONG KONG247.6Dec-04
HONG KONG302.3Dec-04
INDIA321.9166667Dec-04
INDIA308.75Dec-04
INDIA315.1666667Dec-04

<tbody>
</tbody><colgroup><col><col><col></colgroup>
 
Upvote 0
iris1007,

Here is a macro solution for you to consider that is based on your latest screenshots, that uses two arrays in memory that will adjust to he number of raw data rows, and, columns, and, should be fast.

You can change the worksheet names in the macro.

Sample raw data:


Excel 2007
ABCDEFGHIJKLMN
1OPERATORJan-08Feb-08Mar-08Apr-08May-08Jun-08Jul-08Aug-08Sep-08Oct-08Nov-08Dec-08
2JAPAN303.13289.1337.18290.15318.4284.58334.58294.18288.12283.98302.33309.93
3JAPAN267.85307.88312.8294.47309.87295.38279.77277.17167.4746.27265.33242.38
4JAPAN320.85304.37328.97314.33307.27317.38286.85310.03314.27296.7331.98361.9
5JAPAN318.57323.72290.6291.73306.05294.13265.57267.93322.32287.35314.58
6JAPAN311.63310.9329.83259.7308.27299.18303.17326.27318.23352.43
7JAPAN262.43320.2326.95307.5296.28332.87314.65331.1207.75.28279.22
8USA274.9176.32331.73303.28318.12303.3333.17294.98333.53293.15333.9
9USA329.05302.17284.77291.92322.68320.77331.12278.28304.47334.02
10USA331.8307.9352.58269.82324.35305.67331.45298.48218.87311.1312.47345.27
11USA301.6316.45316.08304.48311.25305.15294.13243.77321.13318.8318.82336.32
12USA331.83316.53341.35314.22312.05267.68351.55262277.18326.5310.53358.68
13HONG KONG309.97314.4318.02313.6294.65322.98309.7315.45285.93297.7
14HONG KONG301.88297.65317.13207.45266.95312.82330.45298.05346.65269.58317.13313.68
15HONG KONG312.03337.8322.9298.55320.35284.42302.3302.45323.38322.53329.95
16HONG KONG324.67307.6330.67312.85327.15277.22330.33311.83279.12335.33305.27333.97
17HONG KONG315.08293.63307.48324.38338.67154.25259.6111.57245.355.32247.6
18HONG KONG317.97303.7315.78313.12319.88321.3319.48271.13290.58279296.82302.3
19INDIA323.38291.62240.45258.85284.2274.15305.4317.95329.28344.37309.6321.92
20INDIA294.77310.02340.15313.97315.72315309.55326.8317.65307.6308.75
21INDIA322.45282.17306.07310.47320.37315.5337.98294.53294.27271.88310.65315.17
22
Sheet1


And, after the macro in worksheet Sheet2 (not all rows are shown for brevity):


Excel 2007
ABCD
1OPERATORVolumeMONTH
2JAPAN303.13Jan-08
3JAPAN267.85Jan-08
4JAPAN320.85Jan-08
5JAPAN318.57Jan-08
6JAPAN311.63Jan-08
7JAPAN262.43Jan-08
8USA274.9Jan-08
9USA329.05Jan-08
10USA331.8Jan-08
11USA301.6Jan-08
12USA331.83Jan-08
221HONG KONG297.7Dec-08
222HONG KONG313.68Dec-08
223HONG KONG329.95Dec-08
224HONG KONG333.97Dec-08
225HONG KONG247.6Dec-08
226HONG KONG302.3Dec-08
227INDIA321.92Dec-08
228INDIA308.75Dec-08
229INDIA315.17Dec-08
230
Sheet2


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code:
Sub RearrangeData()
' hiker95, 07/19/2017, ME1015095
Application.ScreenUpdating = False
Dim w1 As Worksheet, w2 As Worksheet
Dim lr As Long, lc As Long, n As Long
Dim a As Variant, i As Long, c As Long
Dim o As Variant, j As Long
Set w1 = Sheets("Sheet1")   '<-- you can change the sheet name here
Set w2 = Sheets("Sheet2")   '<-- you can change the sheet name here
With w1
  lr = .Cells.Find("*", , xlValues, xlWhole, xlByRows, xlPrevious, False).Row
  lc = .Cells.Find("*", , xlValues, xlWhole, xlByColumns, xlPrevious, False).Column
  a = .Range(.Cells(1, 1), .Cells(lr, lc))
  n = Application.Count(.Range(.Cells(2, 2), .Cells(lr, lc)))
  ReDim o(1 To n + 1, 1 To 3)
End With
j = j + 1: o(j, 1) = "OPERATOR": o(j, 2) = "Volume": o(j, 3) = "MONTH"
For c = 2 To UBound(a, 2)
  For i = 2 To UBound(a, 1)
    If Not a(i, c) = vbEmpty Then
      j = j + 1: o(j, 1) = a(i, 1): o(j, 2) = a(i, c): o(j, 3) = a(1, c)
    End If
  Next i
Next c
With w2
  .UsedRange.Clear
  .Cells(1, 1).Resize(UBound(o, 1), UBound(o, 2)) = o
  With .Range("C2").Resize(UBound(o, 1))
    .NumberFormat = "Mmm-dd"
    .Font.Bold = True
  End With
  .Columns.AutoFit
  .Activate
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the RearrangeData macro.
 
Upvote 0
Glad hiker has decided to give you an answer. I will move on and help some place else.
 
Upvote 0
Here is another macro that you can consider...
Code:
[table="width: 500"]
[tr]
	[td]Sub RearrangeData()
  Dim R As Long, C As Long, Data   As Variant
  Data = Worksheets("[COLOR="#0000FF"]Sheet1[/COLOR]").Range("A1").CurrentRegion
  Application.ScreenUpdating = False
  With Worksheets("[B][COLOR="#FF0000"]Sheet2[/COLOR][/B]")
    .UsedRange.Clear
    Range("A1:C1").Value = Array("OPERATOR", "Volume", "Month")
    For C = 2 To UBound(Data, 2)
      With .Cells((UBound(Data) - 1) * (C - 2) + 2, "A").Resize(UBound(Data) - 1)
        .Resize(, 2).Value = Application.Index(Data, Evaluate("ROW(2:" & UBound(Data) + 1 & ")"), Split("1 " & C))
        .Offset(, 2).Value = Data(1, C)
        .Offset(, 2).NumberFormat = "mmm-dd"
      End With
    Next
    On Error GoTo NoBlanks
    .Columns("B").SpecialCells(xlBlanks).EntireRow.Delete
  End With
NoBlanks:
  Application.ScreenUpdating = True
End Sub[/td]
[/tr]
[/table]
Note: I have colored the name of the source sheet in blue and the name of the output sheet in red... change them as needed.
 
Last edited:
Upvote 0
Awesome!!
Thank you both hiker95 and Rick. Both macro does exactly what I need it to do even with my huge data.
Thank you!!
 
Upvote 0
Awesome!!
Thank you both hiker95 and Rick. Both macro does exactly what I need it to do even with my huge data.
Thank you!!
You are quite welcome, I'm glad we were able to have been a help to you.

Just out of curiosity, how huge is your data (how many rows by how many columns)?

Also, is there a noticeable difference between the execution time for our two macros? My gut tells me hiker95's macro should be faster, but I am not entirely sure about that given I minimize, to the extent my method allows, the interaction between my code and the worksheet. Are you able to sense any difference in how long each macro takes to run for the same set of original data?
 
Upvote 0
Awesome!!
Thank you both hiker95 and Rick. Both macro does exactly what I need it to do even with my huge data.
Thank you!!

iris1007,

Thanks for the feedback.

You are very welcome. Glad we could help.

And, come back anytime.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

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