Sorting

[akpflow]

Hello people, I am currrently watching an audiovisual lecture and the tutors used some data on excel to work. I had to downlaod the same data from worldbank dataweb. The data is pulled from the data is not sorted out as the one the tutor used in the video, how do I sort it...

Raw Table Format:

Country Code	Country Name	Series Name	Series Code	2004	2005	2006	2007	2008	2009
Arg	Argentina	Access to Electricity	EG.ELC.ACCS.ZS <colgroup><col width="106"></colgroup><tbody> </tbody>	45	3342	12	345	23
Arg	Argentna	Age dependency ratio (% of working-age population) <colgroup><col width="130"></colgroup><tbody> </tbody>	SP.POP.DPND <colgroup><col width="106"></colgroup><tbody> </tbody>	451	2344

<tbody>
</tbody>

Sorted Table Format

Country Code	Country name	Year	Series Name	Year
Arg	Argentina	2004 <tbody> </tbody>	Access to Electricity <tbody> </tbody>	45
Arg	Argentina	2005	Access to Electricity	3342
Arg	Argentina	2004	Age dependency ratio (% of working-age population)	451

<tbody>
</tbody>

How can I sort this table in this pattern>? Thanks

 

[hiker95]

akpflow,

I assume that your raw data is in worksheet Sheet1, and, that the results should be in a different worksheet, say, a new worksheet Results.

Here is a macro solution for you to consider, that uses two arrays in memory, and, will adjust to the number of raw data rows, and, columns.

The macro will create a new worksheet Results.

You can change the worksheet names in the macro (see the comment lines beginning with the ' characters).

Sample raw:

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K
1	Country Code	Country Name	Series Name	Series Code	2004	2005	2006	2007	2008	2009
2	Arg	Argentina	Access to Electricity	EG.ELC.ACCS.ZS	45	3342	12	345	23
3	Arg	Argentna	Age dependency ratio (% of working-age population)	SP.POP.DPND	451	2344
4
Sheet1

And, after the macro:

Excel 2007
	A	B	C	D	E
1	Country Code	Country Name	Year	Series Name	Value
2	Arg	Argentina	2004	Access to Electricity	45
3	Arg	Argentina	2005	Access to Electricity	3342
4	Arg	Argentina	2006	Access to Electricity	12
5	Arg	Argentina	2007	Access to Electricity	345
6	Arg	Argentina	2008	Access to Electricity	23
7	Arg	Argentna	2004	Age dependency ratio (% of working-age population)	451
8	Arg	Argentna	2005	Age dependency ratio (% of working-age population)	2344
9
Results

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
2. Open your NEW 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
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub ReorgData()
' hiker95, 08/07/2016, ME957339
Dim w1 As Worksheet, wr As Worksheet
Dim a As Variant, i As Long
Dim lr As Long, lc As Long, c As Long, n As Long
Dim o As Variant, j As Long
Application.ScreenUpdating = False

'you can change the raw data sheet name
'                here
'               |------|
Set w1 = Sheets("Sheet1")
With w1
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  lc = .Cells(1, Columns.Count).End(xlToLeft).Column
  a = .Range(.Cells(1, 1), .Cells(lr, lc)).Value
  n = (lr - 1) * (lc - 4)
  ReDim o(1 To n, 1 To 5)
End With
For i = 2 To UBound(a, 1)
  For c = 5 To UBound(a, 2)
    If Not a(i, c) = vbEmpty Then
      j = j + 1
      o(j, 1) = a(i, 1): o(j, 2) = a(i, 2): o(j, 3) = a(1, c): o(j, 4) = a(i, 3)
      o(j, 5) = a(i, c)
    End If
    
  Next c
Next i

'you can change the results sheet name
'                      here, and                                            here
'                     |-------|                                            |-------|
If Not Evaluate("ISREF(Results!A1)") Then Worksheets.Add(After:=w1).Name = "Results"

'               |-------|
Set wr = Sheets("Results")
With wr
  .UsedRange.Clear
  .Cells(1, 1).Resize(, 5).Value = Array("Country Code", "Country Name", "Year", "Series Name", "Value")
  .Cells(2, 1).Resize(UBound(o, 1), UBound(o, 2)) = o
  .Columns(1).Resize(, UBound(o, 2)).AutoFit
  .Activate
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the ReorgData macro.


If you need to see the blank values (in the Results worksheet) from the raw data worksheet, then, I can adjust the macro accordingly.