+ Reply to Thread
Results 1 to 5 of 5

Reshaping data excel to panel data

  1. #1
    Registered User
    Join Date
    11-23-2016
    Location
    rosmalen
    MS-Off Ver
    2013
    Posts
    2

    Reshaping data excel to panel data

    Hello,

    I am new here and currently working on my Finance thesis.
    I have several variables, which have to be reshaped a bit.
    See the attachment for the data.
    On the left is the original and on the right is the way i would like it.
    I have done the example by hand, but is there an easy way to change it?

    Thanks Bob
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: Reshaping data excel to panel data

    Column H =OFFSET($A$2,INT((ROWS($1:1)-1)/3),,,)
    Column I =2013+MOD(ROWS($1:1)-1,3)
    Column J: =OFFSET($B$2,INT((ROWS($1:1)-1)/3),MOD(ROWS($1:1)-1,3),,)
    Attached Files Attached Files
    Glenn



  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    21,056

    Re: Reshaping data excel to panel data

    Another approach that doesn't use volatile functions:

    I inserted a couple of blank columns to move your sample output further to the right, then used these formulae in the cells stated:

    F2: =INDEX(A:A,INT((ROWS($1:1)-1)/3)+2)

    G2: =RIGHT(INDEX($B$1:$D$1,MOD(ROWS($1:1)-1,3)+1),4)

    H2: =VLOOKUP(F2,$A:$D,MOD(ROWS($1:1)-1,3)+2,0)

    Copy down as far as required.

    Hope this helps.

    Pete
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    11-23-2016
    Location
    rosmalen
    MS-Off Ver
    2013
    Posts
    2
    Quote Originally Posted by Glenn Kennedy View Post
    Column H =OFFSET($A$2,INT((ROWS($1:1)-1)/3),,,)
    Column I =2013+MOD(ROWS($1:1)-1,3)
    Column J: =OFFSET($B$2,INT((ROWS($1:1)-1)/3),MOD(ROWS($1:1)-1,3),,)
    Thanks lad!!! You saved me a lot of sleep

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: Reshaping data excel to panel data

    You're welcome.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Panel data.
    By donkyklonk in forum Excel General
    Replies: 3
    Last Post: 03-29-2016, 10:16 AM
  2. Appending Timeseries data into a panel data set
    By ihsan123 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-21-2015, 07:31 PM
  3. [SOLVED] Transpose horizontal data into a vertical data panel
    By Trivmvirate in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-12-2015, 08:46 AM
  4. Problem with Reshaping Excel data for Stata use.
    By alex54 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-07-2014, 10:56 AM
  5. I need help with changing my data set to panel data format!
    By Robbin1234 in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 05-14-2013, 08:55 AM
  6. Reshaping Data
    By GQuinn in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-21-2010, 11:41 PM
  7. Panel Data Regression in Excel
    By eoino in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-06-2010, 09:21 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1