how to transform table into list

M

me huong tra

New Member
Joined
Aug 6, 2011
Messages
5
Hi every one,
I am doing my thesis in financial studies. I have to run a regression of stock index return against rating changes to see their relationship. The problem is I have data input as table, but I need it to be a list so I can run the regression. There are weekly data of 22 countries over 10,5 years resulting in a total of 12000 observations. It is a really huge work for me to transform it manually. I really appreciate if someone could help me. Thanks a lot
I want to attach my data for everyone to take a look but I don't know how to do it :confused: I'm a dummy

INPUT
I have 2 files. The first one is index and the second one is rating.
Index return:

<table style="border-collapse: collapse; width: 288pt;" width="384" border="0" cellpadding="0" cellspacing="0"> <col style="width: 48pt;" width="64" span="6"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl25" style="height: 12.75pt; width: 48pt;" width="64" height="17">Date
</td> <td class="xl25" style="width: 48pt;" width="64"> Argentina</td><td class="xl25" style="width: 48pt;" width="64">
</td> <td class="xl24" style="width: 48pt;" width="64">Brazil</td> <td class="xl24" style="width: 48pt;" width="64">China</td> <td class="xl24" style="width: 48pt;" width="64"> Egypt</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" height="17">1/1/2001</td> <td class="xl27"> 0.05</td> <td class="xl26">
</td> <td class="xl26">0.05
</td> <td class="xl26">0.07
</td> <td class="xl26"> 0.1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" height="17">2/1/2001
</td> <td class="xl27"> 0.06</td> <td class="xl26">
</td> <td class="xl26">-0.04
</td> <td class="xl26">0.07
</td> <td class="xl26"> 0.09</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" height="17">3/1/2001</td> <td class="xl27"> -0.03
</td> <td class="xl26">
</td> <td class="xl26">0.03
</td> <td class="xl26">-0.05
</td> <td class="xl26"> -0.08</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" height="17">4/1/2001
</td> <td class="xl27"> 0.07</td> <td class="xl26">
</td> <td class="xl26">0.06
</td> <td class="xl26">0.12
</td> <td class="xl26"> 0.09</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" height="17">
</td></tr></tbody> </table>
Rating change:

<table style="border-collapse: collapse; width: 288pt;" width="384" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" width="64" span="6"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl25" style="height: 12.75pt; width: 48pt;" width="64" height="17">Date
</td> <td valign="top">
</td><td class="xl25" style="width: 48pt;" width="64">Argentina</td><td class="xl25" style="width: 48pt;" width="64">
</td> <td class="xl24" style="width: 48pt;" width="64">Brazil</td> <td class="xl24" style="width: 48pt;" width="64">China</td> <td class="xl24" style="width: 48pt;" width="64"> Egypt</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" height="17">1/1/2001</td> <td valign="top">
</td><td class="xl27">0</td> <td class="xl26">
</td> <td class="xl26">0
</td> <td class="xl26">0
</td> <td class="xl26"> 0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" height="17">2/1/2001
</td> <td valign="top">
</td><td class="xl27">0</td> <td class="xl26">
</td> <td class="xl26">0
</td> <td class="xl26">0
</td> <td class="xl26"> 1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" height="17">3/1/2001</td> <td valign="top">
</td><td class="xl27">-0.3</td> <td class="xl26">
</td> <td class="xl26">0
</td> <td class="xl26">0.3
</td> <td class="xl26"> 0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" height="17">4/1/2001
</td> <td valign="top">
</td><td class="xl27">0</td> <td class="xl26">
</td> <td class="xl26">+0.7
</td> <td class="xl26">0
</td> <td class="xl26"> 0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" height="17">
</td> <td valign="top">
</td><td class="xl27">
</td> <td class="xl26">
</td> <td class="xl26">
</td> <td class="xl26">
</td></tr></tbody></table>DESIRED OUTPUT
I have to combine 2 file into one list as below
<table width="384" border="0" cellpadding="0" cellspacing="0"><tbody><tr><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td></tr> </tbody></table><table width="384" border="0" cellpadding="0" cellspacing="0"><tbody><tr style="height: 12.75pt;" height="17"> <td class="xl25">Date</td> <td class="xl25">Country
</td> <td class="xl25">Index</td> <td class="xl25">rating change
</td> <td class="xl26">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl27">1/1/2001</td> <td class="xl26"> Argentina</td> <td class="xl26">0.05
</td> <td class="xl26">0</td> <td class="xl26">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl27">2/1/2001</td> <td class="xl26">Argentina
</td> <td class="xl26">0.06</td> <td class="xl26">0
</td> <td class="xl26">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl27">3/1/2001</td> <td class="xl26">Argentina</td> <td class="xl26">-0.03</td> <td class="xl26">-0.3</td> <td class="xl26">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl27">4/1/2001</td> <td class="xl26">Argentina</td> <td class="xl26">0.07</td> <td class="xl26">0</td> <td class="xl26">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl27">1/1/2001</td> <td class="xl26">Brazil
</td> <td class="xl26">0.05</td> <td class="xl26">0</td> <td class="xl26">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl27">2/1/2001</td> <td class="xl26">Brazil
</td> <td class="xl26">-0.04</td> <td class="xl26">0</td> <td class="xl26">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl27">3/1/2001</td> <td class="xl26">Brazil</td> <td class="xl26">0.03</td> <td class="xl26">4</td></tr></tbody></table><table width="384" border="0" cellpadding="0" cellspacing="0"><tbody> </tbody></table>

<table width="384" border="0" cellpadding="0" cellspacing="0" height="36"><tbody> </tbody></table>
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Sort by date Sort by votes
me huong tra,

Thanks for opening your own New Post, and thanks for the Private Message.

I am not able to open the link to your workbook.

To attach screenshots, see below in my Signature block: Post a screen shot with one of these:

If you are not able to give us screenshots, see below in my Signature block: You can upload your workbook to Box Net, and provide us with a link to your workbook.
 
Upvote 0
me huong tra,

It is late.

I will post a new reply in the morning (based on one workbook containg the two worksheets), based on the raw data you have posted in Reply #1.
 
Upvote 0
me huong tra,

Without seeing your original data it is dificult to come up with a correct solution.



Sample raw data in two worksheets in the same workbook (where there are identical entries in each sheet for Date and Country):


Excel Workbook
ABCDE
1DateArgentinaBrazilChinaEgypt
21/1/20010.050.050.070.1
32/1/20010.06-0.040.070.09
43/1/2001-0.030.03-0.05-0.08
54/1/20010.070.060.120.09
6
Index return





Excel Workbook
ABCDE
1DateArgentinaBrazilChinaEgypt
21/1/20010000
32/1/20010001
43/1/2001-0.300.30
54/1/200100.700
6
Rating change





After the macro in worksheet :


Excel Workbook
ABCD
1DateCountryIndexrating Change
21/1/2001Argentina0.050
32/1/2001Argentina0.060
43/1/2001Argentina-0.03-0.3
54/1/2001Argentina0.070
61/1/2001Brazil0.050
72/1/2001Brazil0.060
83/1/2001Brazil-0.03-0.3
94/1/2001Brazil0.070
101/1/2001China0.050
112/1/2001China0.060
123/1/2001China-0.03-0.3
134/1/2001China0.070
141/1/2001Egypt0.050
152/1/2001Egypt0.060
163/1/2001Egypt-0.03-0.3
174/1/2001Egypt0.070
18
Output





If we have some entries in the two worksheets that do not have matching Date and Country, that could look like this:


Excel Workbook
ABCDE
1DateArgentinaBrazilChinaEgypt
21/1/20010.050.050.070.1
32/1/20010.06-0.040.070.09
43/1/2001-0.030.03-0.05-0.08
54/1/20010.070.060.120.09
66/1/20010.050.050.070.1
7
Index return





Excel Workbook
ABCDE
1DateArgentinaBrazilChinaEgypt
21/1/20010000
32/1/20010001
43/1/2001-0.300.30
54/1/200100.700
67/1/20010.050.050.070.1
7
Rating change





After the macro we get:


Excel Workbook
ABCD
1DateCountryIndexrating Change
21/1/2001Argentina0.050
32/1/2001Argentina0.060
43/1/2001Argentina-0.03-0.3
54/1/2001Argentina0.070
66/1/2001Argentina0.05
77/1/2001Argentina0.05
81/1/2001Brazil0.050
92/1/2001Brazil0.060
103/1/2001Brazil-0.03-0.3
114/1/2001Brazil0.070
126/1/2001Brazil0.05
137/1/2001Brazil0.05
141/1/2001China0.050
152/1/2001China0.060
163/1/2001China-0.03-0.3
174/1/2001China0.070
186/1/2001China0.05
197/1/2001China0.05
201/1/2001Egypt0.050
212/1/2001Egypt0.060
223/1/2001Egypt-0.03-0.3
234/1/2001Egypt0.070
246/1/2001Egypt0.05
257/1/2001Egypt0.05
26
Output





Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.


Code: 
Option Explicit
Sub ReorgData()
' hiker95, 08/07/2011
' http://www.mrexcel.com/forum/showthread.php?t=569916
Dim wI As Worksheet, wR As Worksheet, wO As Worksheet
Dim LR As Long, LC As Long, a As Long, NR As Long
Dim DC As Range
Application.ScreenUpdating = False
Set wI = Worksheets("Index return")
Set wR = Worksheets("Rating change")
If Not Evaluate("ISREF(Output!A1)") Then Worksheets.Add(After:=wR).Name = "Output"
Set wO = Worksheets("Output")
wO.UsedRange.Clear
wO.Range("B1:D1") = [{"Date","Country","Index"}]
LR = wI.Cells(Rows.Count, 1).End(xlUp).Row
LC = wI.Cells(1, Columns.Count).End(xlToLeft).Column
For a = 2 To LC Step 1
  NR = wO.Range("B" & Rows.Count).End(xlUp).Offset(1).Row
  wO.Range("B" & NR).Resize(LR - 1).Value = wI.Range("A2:A" & LR).Value
  wO.Range("C" & NR).Resize(LR - 1).Value = wI.Cells(1, a).Value
  wO.Range("D" & NR).Resize(LR - 1).Value = wI.Range("B2:B" & LR).Value
Next a
wO.Range("G1:I1") = [{"Date","Country","rating Change"}]
LR = wR.Cells(Rows.Count, 1).End(xlUp).Row
LC = wR.Cells(1, Columns.Count).End(xlToLeft).Column
For a = 2 To LC Step 1
  NR = wO.Range("G" & Rows.Count).End(xlUp).Offset(1).Row
  wO.Range("G" & NR).Resize(LR - 1).Value = wR.Range("A2:A" & LR).Value
  wO.Range("H" & NR).Resize(LR - 1).Value = wR.Cells(1, a).Value
  wO.Range("I" & NR).Resize(LR - 1).Value = wR.Range("B2:B" & LR).Value
Next a
LR = wO.Cells(Rows.Count, 2).End(xlUp).Row
With wO.Range("A2:A" & LR)
  .FormulaR1C1 = "=RC[1]&RC[2]"
  .Value = .Value
End With
LR = wO.Cells(Rows.Count, 7).End(xlUp).Row
With wO.Range("F2:F" & LR)
  .FormulaR1C1 = "=RC[1]&RC[2]"
  .Value = .Value
End With
LR = wO.Cells(Rows.Count, 1).End(xlUp).Row
wO.Range("A2:D" & LR).Sort Key1:=wO.Range("A2"), Order1:=1, Header:=xlNo
LR = wO.Cells(Rows.Count, 6).End(xlUp).Row
wO.Range("F2:I" & LR).Sort Key1:=wO.Range("F2"), Order1:=1, Header:=xlNo
'Allign two data sets by Date&Country
LR = wO.Cells.Find("*", , xlValues, xlWhole, xlByRows, xlPrevious, False).Row
Set DC = Range("A1:A" & LR)
a = 2
Do While DC.Cells(a, 1) <> ""
  If DC.Cells(a, 1).Offset(, 5) <> "" Then
    If DC.Cells(a, 1) < DC.Cells(a, 1).Offset(, 5) Then
      DC.Cells(a, 1).Offset(, 5).Resize(, 4).Insert -4121
    ElseIf DC.Cells(a, 1) > DC.Cells(a, 1).Offset(, 5) Then
      DC.Cells(a, 1).Resize(, 4).Insert -4121
      LR = LR + 1
      Set DC = Range("A1:A" & LR)
    End If
  End If
  a = a + 1
Loop
LR = wO.Cells.Find("*", , xlValues, xlWhole, xlByRows, xlPrevious, False).Row
For a = 2 To LR Step 1
  If wO.Cells(a, 6) = "" Then
    'do nothing
  ElseIf wO.Cells(a, 6) = wO.Cells(a, 1) Then
    'do nothing
  ElseIf wO.Cells(a, 6) <> "" And wO.Cells(a, 1) = "" Then
    wO.Cells(a, 1).Resize(, 3).Value = wO.Cells(a, 6).Resize(, 3).Value
  End If
Next a
wO.Columns("E:H").Delete
wO.Columns(1).Delete
LR = wO.Cells(Rows.Count, 1).End(xlUp).Row
wO.Range("A2:D" & LR).Sort Key1:=wO.Range("B2"), Order1:=1, Key2:=wO.Range("A2"), Order2:=1, Header:=xlNo
wO.UsedRange.Columns.AutoFit
wO.Activate
Application.ScreenUpdating = True
End Sub


Then run the ReorgData macro.
 
Upvote 0
me huong tra,

You are very welcome.

Glad I could help.

Thanks for the feedback.

Come back anytime.


Are you a teacher cause you gave so detailed and clear instruction.
Click to expand...

I am a retired Information Technology Manager for the largest Accounting Firm in the US.

I have taught many groups of CAAs (Financial Analyst position), "How To Create Excel Macros". These one and two day classes have given them the tools they need to make them more productive and valuable to the Firm.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
623
smozgur
smozgur
B
  • Question Question
Excel Pivot Table to see the number of "Lost Calls"
Replies
0
Views
68
Bernie Galve
B
C
  • Question Question
Picture won't paste in cell overlay
Replies
0
Views
250
Cerka
C
E
  • Question Question
VBA code clean up
Replies
9
Views
583
mumps
mumps
E
  • Question Question
VBA for Web Scraping help
Replies
0
Views
921
epactheactor
E

Forum statistics

Threads
1,224,600
Messages
6,179,836
Members
452,947
Latest member
Gerry_F

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