1

I have data on 1 variable from 1970 to 2017 from all the countries in the world. this data is organized as one large vertical list, with first a column with the (repeating) country name, then a column with 1970 till 2017, repeating and the third column the variable values. however, I need it to be organized in another format (in order to be able to merge the variable with other files in spss). I need the left column to have all the countries, but just one. Then horizontal a row with 1970-2017 and below that all the rows with the per country variable values

I added 2 photos of how it looks now, and one photo of how it should be organized. how do I get there without having to select all the countries separately and flipping that data?

How it looks now:
how it looks now

Also how it looks now:
also how it looks now

How it should look:
how it should look!

3 Answers 3

0

You'll need a pivot table for this. Select all columns, there is an option to start the Pivot Table wizard (where depends on your version of Excel):

enter image description here

Specify which field you need in the columns ('year'), rows ('code' and 'country') and the values ('KOFGI'):

enter image description here

You're almost there; all you need is an option 'Show in tabular form' and to disable the subtotals in the rows. (Again, where those are depends on your Excel version.) The final result is as follows:

enter image description here

3
  • Thank you! I cannot find the ''summarise with pivot table'' option, does it also work with just selecting all the data and then creating a pivot table? I seem te get the same result that way, except for the country codes in an odd maner below the countries, instead of just in their own colums, but that may very well be bc of other reasons
    – user279711
    Apr 7, 2020 at 19:15
  • I guess so, there are various ways to create a pivot table.
    – Glorfindel
    Apr 7, 2020 at 19:17
  • @user279711 yes create pivot table is the same as summarize with pivot table, your on windows, answer was provided from an apple product. for your other issue, you need to go on report layout, and select tabular view, and maybe turn off subtotals, you can these in options' in picture 3 of the answer here
    – PeterH
    Apr 7, 2020 at 19:20
0

If you are interested in a VBA solution you can try this, just insert in a module and link to a button on sheet2 (modify the Sheet1 and Sheet2 to suit in the code) - where sheet 2 is blank:

Sub MyTpose()
Application.Screenupdating = False
Dim ws1 As Worksheet: Set ws1 = Sheet1
Dim ws2 As Worksheet: Set ws2 = Sheet2
Dim MyINT As Long, i As Long, MyCOUNT As Long, LstRW As Long
'\\ MyINT reflects 1970 to 2017 in this case, modify to suit.
MyINT = 47
ws2.Cells(4, 2) = ws1.Cells(1, 1)
ws2.Cells(4, 1) = ws1.Cells(1, 2)
ws2.Cells(4, 3) = "INDICATOR X"
ws2.Cells(4, 4) = "INDICATOR Y"
ws2.Cells(1, 1) = "Data Source"
ws2.Cells(1, 2) = "World Development Indicators"
ws2.Cells(2, 1) = "Last Updated"
ws2.Cells(2, 2) = Now
ws2.Cells(4, 5).Resize(1, MyINT) = Application.WorksheetFunction.Transpose(ws1.Range(ws1.Cells(2, 3), ws1.Cells(2 + MyINT, 3)))
MyCOUNT = (ws1.Cells(1, 1).CurrentRegion.Rows.Count - 1) / MyINT
For i = 2 To ws1.Cells(1, 1).CurrentRegion.Rows.Count - 1 Step MyINT
    LstRW = ws2.Cells(4, 1).CurrentRegion.Rows.Count + 4
    ws2.Cells(LstRW, 1) = ws1.Cells(i, 2)
    ws2.Cells(LstRW, 2) = ws1.Cells(i, 1)
    ws2.Cells(LstRW, 3) = "AGE"
    ws2.Cells(LstRW, 4) = "SP.POP.DPND"
    ws2.Cells(LstRW, 5).Resize(1, MyINT) = Application.WorksheetFunction.Transpose(ws1.Range(ws1.Cells(i, 4), ws1.Cells(i + MyINT, 4)))
Next i
End Sub

Note that the MyINT variable reflects the number of years you are transposing, and assumes that all of your entries have the same number of years. If this is not the case then there will be a way to calculate the offsets within the loop but I have assumed consistency for now. Other assumptions are that there will always be a blank line in row 3 of the output table (otherwise the currentregion.rows.count will fail). I was not sure where you were getting the AGE and other indicator so I have just inserted them as strings.

0

If you have Windows Excel 2010+, you can use Power Query. It is included in later versions as Get & Transform on the Data tab, and in the earlier versions as a free add-in from Microsoft.

In the later versions, with a single cell selected in your data table you would navigate to:

  • Data → Get & Transform Data → From Table/Range
    • Ensure the dotted lines encompass only the table, and not any extraneous rows/columns
  • The PQ editor will open, showing your original table, and probably a Changed Type in the Applied Steps window to the right.
  • Sort the Year column ascending
  • Select the Year column, then
    • Pivot
      • Values Column = KOFGI
      • Advanced Options: Don't Aggregate

enter image description here

  • Rearrange the Country Name and Country Code columns as you show in your output.

And you're done!

enter image description here

This is the generated M-Code, but will be different on your machine due to a different Table name, and extra columns that you didn't seem to include in your data source

let
    Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Country Code", type text}, {"Country Name", type text}, {" Year", Int64.Type}, {"KOFGI", type number}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{" Year", Order.Ascending}}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Sorted Rows", {{" Year", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Sorted Rows", {{" Year", type text}}, "en-US")[#" Year"]), " Year", "KOFGI"),
    #"Reordered Columns" = Table.ReorderColumns(#"Pivoted Column",{"Country Name", "Country Code", "2010", "2011", "2012", "2013", "2014", "2015", "2016"})
in
    #"Reordered Columns"

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .