Rearranging data

sannuk7 · Apr 18th 2016

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.

**alansidman** · Apr 18th 2016

Re: rearraging data

try this:

Code

Option Explicit




Sub trans()
    Dim s1 As Worksheet, s2 As Worksheet
    Set s1 = Sheets("Sheet1")
    Set s2 = Sheets("Sheet2")
    Dim r1 As Long, r2 As Long, i As Long
    r1 = s1.Range("A" & Rows.Count).End(xlUp).Row




    Application.ScreenUpdating = False
    s1.Range("A2").Copy s2.Range("A1")
    s1.Range("A1").Copy s2.Range("B1")
    s1.Range("C2:D2").Copy s2.Range("C1")
    For i = 3 To r1
        r2 = s2.Range("A" & Rows.Count).End(xlUp).Row
        s1.Range("A" & i).Copy s2.Range("A" & r2 + 1)
        s1.Range("B1").Copy s2.Range("B" & r2 + 1)
        s1.Range("B" & i & ":C" & i).Copy s2.Range("C" & r2 + 1)
        s1.Range("A" & i).Copy s2.Range("A" & r2 + 2)
        s1.Range("D1").Copy s2.Range("B" & r2 + 2)
        s1.Range("D" & i & ":E" & i).Copy s2.Range("C" & r2 + 2)
        Application.CutCopyMode = False
    Next i
    Application.ScreenUpdating = False
    MsgBox "complete"
End Sub

Display More

sannuk7 · Apr 18th 2016

Re: rearraging data

Quote from sannuk7;769421

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="align: center"]A[/TD]
[TD="width: 64, bgcolor: #ECF0F0, align: center"]B[/TD]
[TD="width: 64, bgcolor: #ECF0F0, align: center"]C[/TD]
[TD="width: 64, bgcolor: #ECF0F0, align: center"]D[/TD]
[TD="width: 64, bgcolor: #ECF0F0, align: center"]E[/TD]
[/tr]

[tr]

[TD="bgcolor: #ECF0F0, align: center"]1[/TD]
[TD="bgcolor: #FFFFFF"]year[/TD]
[TD="bgcolor: #FFFFFF"]2000[/TD]
[TD="bgcolor: #FFFFFF"]2000[/TD]
[TD="bgcolor: #FFFFFF"]2001[/TD]
[TD="bgcolor: #FFFFFF"]2001[/TD]
[/tr]

[tr]

[TD="bgcolor: #ECF0F0, align: center"]2[/TD]
[TD="bgcolor: #FFFFFF"]Company[/TD]
[TD="bgcolor: #FFFFFF"]sales[/TD]
[TD="bgcolor: #FFFFFF"]R&D[/TD]
[TD="bgcolor: #FFFFFF"]sales[/TD]
[TD="bgcolor: #FFFFFF"]R&D[/TD]
[/tr]

[tr]

[TD="bgcolor: #ECF0F0, align: center"]3[/TD]
[TD="bgcolor: #FFFFFF"]A[/TD]
[TD="bgcolor: #FFFFFF"]500[/TD]
[TD="bgcolor: #FFFFFF"]5[/TD]
[TD="bgcolor: #FFFFFF"]600[/TD]
[TD="bgcolor: #FFFFFF"]7[/TD]
[/tr]

[tr]

[TD="bgcolor: #ECF0F0, align: center"]4[/TD]
[TD="bgcolor: #FFFFFF"]B[/TD]
[TD="bgcolor: #FFFFFF"]600[/TD]
[TD="bgcolor: #FFFFFF"]4[/TD]
[TD="bgcolor: #FFFFFF"]900[/TD]
[TD="bgcolor: #FFFFFF"]3[/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="align: center"]A[/TD]
[TD="width: 64, bgcolor: #ECF0F0, align: center"]B[/TD]
[TD="width: 64, bgcolor: #ECF0F0, align: center"]C[/TD]
[TD="width: 64, bgcolor: #ECF0F0, align: center"]D[/TD]
[/tr]

[tr]

[TD="bgcolor: #ECF0F0, align: center"]1[/TD]
[TD="bgcolor: #FFFFFF"]Company[/TD]
[TD="bgcolor: #FFFFFF"]year[/TD]
[TD="bgcolor: #FFFFFF"]Sales[/TD]
[TD="bgcolor: #FFFFFF"]R&D[/TD]
[/tr]

[tr]

[TD="bgcolor: #ECF0F0, align: center"]2[/TD]
[TD="bgcolor: #FFFFFF"]A[/TD]
[TD="bgcolor: #FFFFFF"]2000[/TD]
[TD="bgcolor: #FFFFFF"]500[/TD]
[TD="bgcolor: #FFFFFF"]5[/TD]
[/tr]

[tr]

[TD="bgcolor: #ECF0F0, align: center"]3[/TD]
[TD="bgcolor: #FFFFFF"]A[/TD]
[TD="bgcolor: #FFFFFF"]2001[/TD]
[TD="bgcolor: #FFFFFF"]600[/TD]
[TD="bgcolor: #FFFFFF"]7[/TD]
[/tr]

[tr]

[TD="bgcolor: #ECF0F0, align: center"]4[/TD]
[TD="bgcolor: #FFFFFF"]B[/TD]
[TD="bgcolor: #FFFFFF"]2000[/TD]
[TD="bgcolor: #FFFFFF"]600[/TD]
[TD="bgcolor: #FFFFFF"]4[/TD]
[/tr]

[tr]

[TD="bgcolor: #ECF0F0, align: center"]5[/TD]
[TD="bgcolor: #FFFFFF"]B[/TD]
[TD="bgcolor: #FFFFFF"]2001[/TD]
[TD="bgcolor: #FFFFFF"]900[/TD]
[TD="bgcolor: #FFFFFF"]3[/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.

Display More

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

sannuk7 · Apr 18th 2016

Re: rearraging data

Thanks a lot. it has worked and made my simple.
Right now I have two variables "Sales" and "R&D". Kindly help me how to do this when I have say 6 variables.
Thanks

**alansidman** · Apr 18th 2016

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.

sannuk7 · Apr 19th 2016

Re: Rearranging data

Hi,
I have attached the excel file containing data. The first worksheet is having the raw data. Whereas, the second worksheet shows the data I need. In second worksheet I have showed only for two companies but I need it all the companies.
In future I would provide the full details.
Thanks and Regards.

Rearranging data

Participate now!

Share