VBA macro to Arrange data in different form

S

StupidCoffee

New Member
Joined
Jun 11, 2013
Messages
16
I am trying to organize my raw data into a different form chart to help me build a special trend.
I have one sheet with name, date, and shortened names for each component.
As a result of this macro I need a table that shows name, description (which will be repeated), date, and value of the component

Here is a sample

Raw:
[TABLE="width: 338"]
<colgroup><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD]Name[/TD]
[TD]Date[/TD]
[TD].H[/TD]
[TD].O[/TD]
[TD].N[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]3/12/2013[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]5/10/2013[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]6/2/2013[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]1/22/2013[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]3/15/2013[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]5/20/2013[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]18
[/TD]
[/TR]
</tbody>[/TABLE]


Result:
[TABLE="width: 274"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Name[/TD]
[TD]Description[/TD]
[TD]Date[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD]A.H[/TD]
[TD]Hydrogen[/TD]
[TD="align: right"]3/12/2013[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]A.O[/TD]
[TD]Oxygen[/TD]
[TD="align: right"]3/12/2013[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]A.N[/TD]
[TD]Nitrogen[/TD]
[TD="align: right"]3/12/2013[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]A.H[/TD]
[TD]Hydrogen[/TD]
[TD="align: right"]5/10/2013[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]A.O[/TD]
[TD]Oxygen[/TD]
[TD="align: right"]5/10/2013[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]A.N[/TD]
[TD]Nitrogen[/TD]
[TD="align: right"]5/10/2013[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]A.H[/TD]
[TD]Hydrogen[/TD]
[TD="align: right"]6/2/2013[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]A.O[/TD]
[TD]Oxygen[/TD]
[TD="align: right"]6/2/2013[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]A.N[/TD]
[TD]Nitrogen[/TD]
[TD="align: right"]6/2/2013[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD]B.H[/TD]
[TD]Hydrogen[/TD]
[TD="align: right"]1/22/2013[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]B.O[/TD]
[TD]Oxygen[/TD]
[TD="align: right"]1/22/2013[/TD]
[TD="align: right"]11[/TD]
[/TR]
[TR]
[TD]B.N[/TD]
[TD]Nitrogen[/TD]
[TD="align: right"]1/22/2013[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD]B.H[/TD]
[TD]Hydrogen[/TD]
[TD="align: right"]3/15/2013[/TD]
[TD="align: right"]13[/TD]
[/TR]
[TR]
[TD]B.O[/TD]
[TD]Oxygen[/TD]
[TD="align: right"]3/15/2013[/TD]
[TD="align: right"]14[/TD]
[/TR]
[TR]
[TD]B.N[/TD]
[TD]Nitrogen[/TD]
[TD="align: right"]3/15/2013[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD]B.H[/TD]
[TD]Hydrogen[/TD]
[TD="align: right"]5/20/2013[/TD]
[TD="align: right"]16[/TD]
[/TR]
[TR]
[TD]B.O[/TD]
[TD]Oxygen[/TD]
[TD="align: right"]5/20/2013[/TD]
[TD="align: right"]17[/TD]
[/TR]
[TR]
[TD]B.N[/TD]
[TD]Nitrogen[/TD]
[TD="align: right"]5/20/2013[/TD]
[TD="align: right"]18[/TD]
[/TR]
</tbody>[/TABLE]


I tried using INDEX function, but I need a macro code for this.

Please help..
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Sort by date Sort by votes
StupidCoffee said:
Actually, the description column is not needed
Click to expand...
This assumes your data start in A2 and produces a rearranged version (including description) beginning in H2 of the same sheet:
Code: 
Sub RearrangeMyMolecules()
Dim lR As Long, R As Range, vA As Variant, Hdrs As Variant, nR As Long, O(1 To _
    4) As Variant, S As Variant, SF As Variant
lR = Range("A" & Rows.Count).End(xlUp).Row
Set R = Range("A2", "E" & lR)
vA = R.Value
Hdrs = Array("Name", "Description", "Date", "Value")
S = Array(".H", ".O", ".N")
SF = Array("Hydrogen", "Oxygen", "Nitrogen")
Range("H1:K1").Value = Hdrs
Application.ScreenUpdating = False
For i = LBound(vA, 1) To UBound(vA, 1)
    For j = 0 To 2
        nR = Range("H" & Rows.Count).End(xlUp).Row + 1
        O(1) = vA(i, 1) & S(j)
        O(2) = SF(j)
        O(3) = vA(i, 2)
        O(4) = vA(i, j + 3)
        Range("H" & nR).Resize(1, 4).Value = O
        Erase O
    Next j
Next i
Columns("H:K").AutoFit
End Sub
 
Upvote 0
Thank you so much!

If I want to produce the result on a new sheet, would I just change the Range("H1:K1") to a desired sheet and range?
 
Upvote 0
StupidCoffee said:
Thank you so much!

If I want to produce the result on a new sheet, would I just change the Range("H1:K1") to a desired sheet and range?
Click to expand...
This will create a new sheet named "RearrangedData" and put the results on it starting in A1 if you run the code from your raw data sheet. Each time you run the code it will replace the contents of the RearrangedData sheet.
Code: 
Sub RearrangeMyMolecules()
'Run this code from the sheet the raw data are on
Dim lR As Long, R As Range, vA As Variant, Hdrs As Variant, nR As Long, O(1 To _
    4) As Variant, S As Variant, SF As Variant, sSht As Worksheet
    
Set sSht = ActiveSheet
lR = sSht.Range("A" & Rows.Count).End(xlUp).Row
Set R = sSht.Range("A2", "E" & lR)
vA = R.Value
Hdrs = Array("Name", "Description", "Date", "Value")
S = Array(".H", ".O", ".N")
SF = Array("Hydrogen", "Oxygen", "Nitrogen")
Application.ScreenUpdating = False
Application.DisplayAlerts = False
On Error Resume Next
Sheets("RearrangedData").Delete
On Error GoTo 0
Sheets.Add after:=sSht
ActiveSheet.Name = "RearrangedData"
With Sheets("RearrangedData")
    .Range("A1:D1").Value = Hdrs
    For i = LBound(vA, 1) To UBound(vA, 1)
        For j = 0 To 2
            nR = .Range("A" & Rows.Count).End(xlUp).Row + 1
            O(1) = vA(i, 1) & S(j)
            O(2) = SF(j)
            O(3) = vA(i, 2)
            O(4) = vA(i, j + 3)
            .Range("A" & nR).Resize(1, 4).Value = O
            Erase O
        Next j
    Next i
    .Columns("A:D").AutoFit
End With
sSht.Select
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
Ranking Invoices By Highest Amount By "Year to Date"
Replies
2
Views
126
ellison
E
A
  • Question Question
Make a table from data which updated monthly.
Replies
1
Views
341
adnan1975
A
H
  • Solved
Insert a blank row when the cell in column "B" changes.
2
Replies
10
Views
1K
Peter_SSs
Peter_SSs
R
  • Solved
Very Complex- How to Find Stock Market Probability of 3, 4 & 5 negative days (Find Results of Day 4,5&6)
Replies
8
Views
601
rebel123
R
Lacan
  • Solved
Filter data and return it with text short arrangements
Replies
5
Views
210
myall_blues
myall_blues

Forum statistics

Threads
1,223,244
Messages
6,170,976
Members
452,372
Latest member
Natalie18

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top