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
Re-arranging data in excel
-
- 2StarLounger
- Posts: 181
- Joined: 19 Feb 2016, 16:54
- Location: Veraval, India
Re-arranging data in excel
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78391
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Re-arranging data in excel
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
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 181
- Joined: 19 Feb 2016, 16:54
- Location: Veraval, India
Re: Re-arranging data in excel
Thanks a lot Hans, this saves a lot time.
-
- 2StarLounger
- Posts: 144
- Joined: 11 Jun 2012, 20:37
Re: Re-arranging data in excel
Power query solution at cell D1
You do not have the required permissions to view the files attached to this post.