Re-arranging data in excel

shreeram.maroo
2StarLounger
Posts: 122
Joined: 19 Feb 2016, 16:54
Location: Veraval, India

Re-arranging data in excel

Post by shreeram.maroo »

Hi,

I have a raw data in excel which is in the shape and format as per 'Data' tab of attached excel file and i want to rearrange that data in the manner as mentioned in 'Result'. Is there a work around possible instead of manually copy-pasting the data. The attached file includes only 4 IDs and respective dates. I have to do it for around 11000 IDs.

Many thanks in advance.

Regards
Shreeram
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 69454
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Re-arranging data in excel

Post by HansV »

Here is a macro that you can run:

Code: Select all

Sub Rearrange()
    Dim wshS As Worksheet
    Dim wshT As Worksheet
    Dim arrS() As Variant
    Dim arrT() As Variant
    Dim m As Long
    Dim n As Long
    Dim s As Long
    Dim c As Long
    Dim t As Long
    Set wshS = Worksheets("Data")
    arrS = wshS.UsedRange.Value
    m = UBound(arrS, 1)
    n = UBound(arrS, 2)
    ReDim arrT(1 To m * n, 1 To 2)
    For c = 1 To n
        For s = 2 To m
            t = t + 1
            arrT(t, 1) = arrS(1, c)
            arrT(t, 2) = arrS(s, c)
        Next s
        t = t + 1
    Next c
    Set wshT = Worksheets("Result")
    Application.ScreenUpdating = False
    wshT.Range("A1").Resize(m * n, 2).Value = arrT
    Application.ScreenUpdating = True
End Sub
Regards,
Hans

shreeram.maroo
2StarLounger
Posts: 122
Joined: 19 Feb 2016, 16:54
Location: Veraval, India

Re: Re-arranging data in excel

Post by shreeram.maroo »

Thanks a lot Hans, this saves a lot time.

p45cal
Lounger
Posts: 40
Joined: 11 Jun 2012, 20:37

Re: Re-arranging data in excel

Post by p45cal »

Power query solution at cell D1
You do not have the required permissions to view the files attached to this post.