Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

How to re arrange data in Excel sheet

Posted on 2016-09-21
Medium Priority
72 Views
I added three columns to the old format as attached here. Could you please modify the code. I will create a new question for this.

Regards,

Dallag
ReArrangeData_V41.xlsm
0
Question by:Mohammed Dallag
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 4
• 2

LVL 32

Accepted Solution

Subodh Tiwari (Neeraj) earned 2000 total points
ID: 41808449
I have added the VBA topic to the question.

Please try the following code and see if you get the desired output....

``````Sub ReArrangeDataVersion4()
Dim sws As Worksheet, dws As Worksheet
Dim lr As Long, lc As Long, i As Long, dlr As Long
Dim x, y
Dim TimeTaken As Date
TimeTaken = Now
Application.ScreenUpdating = False
Set sws = Sheets("Sheet1")
Set dws = Sheets("Output")

lr = sws.Cells(Rows.Count, 1).End(xlUp).Row
lc = sws.Cells(1, Columns.Count).End(xlToLeft).Column

dlr = dws.Cells(Rows.Count, 2).End(xlUp).Row
If dlr > 1 Then dws.Range("A2:G" & dlr).Clear
y = sws.Range("A4:A" & lr).Value

For i = 2 To lc Step 8
DoEvents
dlr = dws.Range("B" & Rows.Count).End(3)(2).Row
dws.Range("B" & dlr).Offset(0, -1) = sws.Cells(1, i)
dws.Range("B" & dlr).Resize(UBound(y, 1)).Value = y
x = sws.Range(sws.Cells(4, i), sws.Cells(lr, i + 7)).Value
dws.Range("C" & dlr).Resize(UBound(y, 1), 8).Value = x
Next i
dlr = dws.Cells(Rows.Count, 2).End(xlUp).Row
dws.Range("A2:A" & dlr).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
dws.Range("A2:A" & dlr).Value = dws.Range("A2:A" & dlr).Value
dws.Columns.AutoFit
dws.Range("A1").CurrentRegion.Borders.Color = vbBlack
dws.Activate
Application.ScreenUpdating = True
MsgBox "Time taken to process data was " & Format(Now - TimeTaken, "hh:mm:ss")
End Sub
``````
0

Author Closing Comment

ID: 41808469
Thank you so much
0

LVL 32

Expert Comment

ID: 41808485
0

Author Comment

ID: 41819230
Dear Subodh Tiwari (Neeraj)

I need to insert the results in Access file because of the excel sheet limitation. I will create a new question for that.

Regards,

Dallag
0

Author Comment

ID: 41819308
0

Author Comment

ID: 41823800
Dear Neeraj,

Could you help me in moving the output sheet to access. I created a new question for that.

https://www.experts-exchange.com/questions/28972908/How-to-insert-the-result-of-excel-code-to-access.html

Regards,

Dallag
0

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Suggested Courses
Course of the Month7 days, 17 hours left to enroll