Rearranging data

  • I have data in the format
    year 2000 2000 2001 2001
    Company sales R&D sales R&D
    A 500 5 600 7
    B 600 4 900 3
    [table="class:thin_grid"]

    [tr][td]

    v

    [/td]


    [td="bgcolor:#ECF0F0, align:center,width:103"]A[/td]
    [td="bgcolor:#ECF0F0, align:center,width:64"]B[/td]
    [td="bgcolor:#ECF0F0, align:center,width:64"]C[/td]
    [td="bgcolor:#ECF0F0, align:center,width:64"]D[/td]
    [td="bgcolor:#ECF0F0, align:center,width:64"]E[/td]

    [/tr][tr]

    [td="bgcolor:#ECF0F0, align:center"]1[/td]
    [td="bgcolor:#FFFFFF, align:LEFT"][COLOR="#3E3E3E"]year[/COLOR][/td]
    [td="bgcolor:#FFFFFF, align:RIGHT"][COLOR="#000000"]2000[/COLOR][/td]
    [td="bgcolor:#FFFFFF, align:RIGHT"][COLOR="#000000"]2000[/COLOR][/td]
    [td="bgcolor:#FFFFFF, align:RIGHT"][COLOR="#000000"]2001[/COLOR][/td]
    [td="bgcolor:#FFFFFF, align:RIGHT"][COLOR="#000000"]2001[/COLOR][/td]

    [/tr]


    [tr]

    [td="bgcolor:#ECF0F0, align:center"]2[/td]
    [td="bgcolor:#FFFFFF, align:LEFT"][COLOR="#3E3E3E"]Company[/COLOR][/td]
    [td="bgcolor:#FFFFFF, align:LEFT"][COLOR="#000000"]sales[/COLOR][/td]
    [td="bgcolor:#FFFFFF, align:LEFT"][COLOR="#000000"]R&D[/COLOR][/td]
    [td="bgcolor:#FFFFFF, align:LEFT"][COLOR="#000000"]sales[/COLOR][/td]
    [td="bgcolor:#FFFFFF, align:LEFT"][COLOR="#000000"]R&D[/COLOR][/td]

    [/tr]


    [tr]

    [td="bgcolor:#ECF0F0, align:center"]3[/td]
    [td="bgcolor:#FFFFFF, align:LEFT"][COLOR="#3E3E3E"]A[/COLOR][/td]
    [td="bgcolor:#FFFFFF, align:RIGHT"][COLOR="#000000"]500[/COLOR][/td]
    [td="bgcolor:#FFFFFF, align:RIGHT"][COLOR="#000000"]5[/COLOR][/td]
    [td="bgcolor:#FFFFFF, align:RIGHT"][COLOR="#000000"]600[/COLOR][/td]
    [td="bgcolor:#FFFFFF, align:RIGHT"][COLOR="#000000"]7[/COLOR][/td]

    [/tr]


    [tr]

    [td="bgcolor:#ECF0F0, align:center"]4[/td]
    [td="bgcolor:#FFFFFF, align:LEFT"][COLOR="#3E3E3E"]B[/COLOR][/td]
    [td="bgcolor:#FFFFFF, align:RIGHT"][COLOR="#000000"]600[/COLOR][/td]
    [td="bgcolor:#FFFFFF, align:RIGHT"][COLOR="#000000"]4[/COLOR][/td]
    [td="bgcolor:#FFFFFF, align:RIGHT"][COLOR="#000000"]900[/COLOR][/td]
    [td="bgcolor:#FFFFFF, align:RIGHT"][COLOR="#000000"]3[/COLOR][/td]

    [/tr]


    [/table]
    I am trying to rearrange it into following format
    Company year Sales R&D
    A 2000 500 5
    A 2001 600 7
    B 2000 600 4
    B 2000 900 3
    [table="class:thin_grid"]

    [tr][td]

    v

    [/td]


    [td="bgcolor:#ECF0F0, align:center,width:103"]A[/td]
    [td="bgcolor:#ECF0F0, align:center,width:64"]B[/td]
    [td="bgcolor:#ECF0F0, align:center,width:64"]C[/td]
    [td="bgcolor:#ECF0F0, align:center,width:64"]D[/td]

    [/tr][tr]

    [td="bgcolor:#ECF0F0, align:center"]1[/td]
    [td="bgcolor:#FFFFFF, align:LEFT"][COLOR="#3E3E3E"]Company[/COLOR][/td]
    [td="bgcolor:#FFFFFF, align:LEFT"][COLOR="#000000"]year[/COLOR][/td]
    [td="bgcolor:#FFFFFF, align:LEFT"][COLOR="#000000"]Sales[/COLOR][/td]
    [td="bgcolor:#FFFFFF, align:LEFT"][COLOR="#000000"]R&D[/COLOR][/td]

    [/tr]


    [tr]

    [td="bgcolor:#ECF0F0, align:center"]2[/td]
    [td="bgcolor:#FFFFFF, align:LEFT"][COLOR="#3E3E3E"]A[/COLOR][/td]
    [td="bgcolor:#FFFFFF, align:RIGHT"][COLOR="#000000"]2000[/COLOR][/td]
    [td="bgcolor:#FFFFFF, align:RIGHT"][COLOR="#000000"]500[/COLOR][/td]
    [td="bgcolor:#FFFFFF, align:RIGHT"][COLOR="#000000"]5[/COLOR][/td]

    [/tr]


    [tr]

    [td="bgcolor:#ECF0F0, align:center"]3[/td]
    [td="bgcolor:#FFFFFF, align:LEFT"][COLOR="#3E3E3E"]A[/COLOR][/td]
    [td="bgcolor:#FFFFFF, align:RIGHT"][COLOR="#000000"]2001[/COLOR][/td]
    [td="bgcolor:#FFFFFF, align:RIGHT"][COLOR="#000000"]600[/COLOR][/td]
    [td="bgcolor:#FFFFFF, align:RIGHT"][COLOR="#000000"]7[/COLOR][/td]

    [/tr]


    [tr]

    [td="bgcolor:#ECF0F0, align:center"]4[/td]
    [td="bgcolor:#FFFFFF, align:LEFT"][COLOR="#3E3E3E"]B[/COLOR][/td]
    [td="bgcolor:#FFFFFF, align:RIGHT"][COLOR="#000000"]2000[/COLOR][/td]
    [td="bgcolor:#FFFFFF, align:RIGHT"][COLOR="#000000"]600[/COLOR][/td]
    [td="bgcolor:#FFFFFF, align:RIGHT"][COLOR="#000000"]4[/COLOR][/td]

    [/tr]


    [tr]

    [td="bgcolor:#ECF0F0, align:center"]5[/td]
    [td="bgcolor:#FFFFFF, align:LEFT"][COLOR="#000000"]B[/COLOR][/td]
    [td="bgcolor:#FFFFFF, align:RIGHT"][COLOR="#000000"]2001[/COLOR][/td]
    [td="bgcolor:#FFFFFF, align:RIGHT"][COLOR="#000000"]900[/COLOR][/td]
    [td="bgcolor:#FFFFFF, align:RIGHT"][COLOR="#000000"]3[/COLOR][/td]

    [/tr]


    [/table]
    I am unable to do it because I have headers in second row too. I need this so that I can further run the data for statistical analysis, but I have been stuck here for 10 days.
    Kindly help me.

  • Re: rearraging data


    try this:


  • Re: rearraging data



    Thank you very much. It has worked like magic.
    Kindly help me with one more query. In this data I have only two variables "sales" and "R&D". But how to rearrange the data if I have six or ten variables.


    Thanks

  • Re: Rearranging data


    Suggest you upload a sample worksheet so that forum responders may make a serious attempt to resolve your specific issue. In the future, I suggest you tell us the full story in advance as what you are asking for now requires different thought process than what you originally requested.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!