Create panel data with multiple variables

in reference to this link: https://www.excelforum.com/excel-gen...-into-one.html
i would like to ask how to create panel data if we have multiple variables in a sheet as:

company 2006 2006 2006 2007 2007 2007 2008 2008 2008 2009 2009 2009 2010 2010 2010
EPS ROE ROA EPS ROE ROA EPS ROE ROA EPS ROE ROA EPS ROE ROA

PLEASE PROVIDE ANSWER, HELP ME IN DOING THIS AS
COMPANY EPS ROE ROA
X 2006
X 2007
X 2008
X 2009
X 2010
Y 2006
Y 2007
Y 2008
Y 2009
Y 2010
Z 2006
Z 2007
Z 2008
Z 2009
Z 2010

The formatting in the post is not very clear, so it would help if you attached a sample Excel workbook.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Please note that the Paperclip icon does not work.

Hope this helps.

Pete

PFA file "my data"

I've attached a formula solution which is quite generic - if you were to add 3 new columns to Sheet1 for 2011 (and subsequent years), then it will automatically adjust. Here's the formulae I used in the Required sheet:

A2: =INDEX(Sheet1!A:A,INT((ROWS($1:1)-1)/(COUNT(Sheet1!$1:$1)/3))+3)&""

which returns the company names for as many times as you have years (block of 3 columns) in row 1 of Sheet1

B2: =IF(A2="","",YEAR(INDEX(Sheet1!$1:$1,MOD(ROWS($1:1)-1,COUNT(Sheet1!$1:$1)/3)*3+2)))

which returns the appropriate range of years for each company

C2: =IF($A2="","",INDEX(Sheet1!$B$3:$P$22,MATCH($A2,Sheet1!$A$3:$A$22,0),MATCH(C$1,Sheet1!$B$2:$P$2,0)+(COUNTIF($A$2:$A2,$A2)-1)*3))

which returns the appropriate value for the EPS amounts. This formula can be copied across into D2:E2 to return the P/E and P/B data.

Note that in Sheet1 you had spaces after EPS, P/E and P/B, which I have removed. I have also applied a Custom Format to cells C2:E2 of General;-General;, in order to show blank values in Sheet1 as blanks.

The formulae in A2:E2 can then be copied down as far as you need to - I've copied to row 105 in the example file.

Hope this helps.

Pete

Hi Marvin,

I can't do that, as I'm using XL2007 (yes, cheapskate !!)

Pete

Thanks for the rep, Marvin.

Three formulae, and it's done - what's the problem ?

Many of the 3's that appear in the formulae (i.e. /3 and *3) come about because that is the number of columns in each yearly block, so if there were more then the 3 can be changed as appropriate (or changed to a COUNTA expression). The +3 in the first formula relates to the first row where the data starts. Consequently, the solution could be made even more generic for other cross-tab reports.

Pete

Hi Pete

Thank you so much, this is great help.

I have data for years 2011-2016, and i tried to add these years and get the result but could not get the answer. PFA the sheet and guide me where am i wrong.

Originally Posted by MarvinP

... Why are our answers different? ...

Hi Marvin,

Your data is laid out differently, by having a Marker column with EPS, P/E and P/B on each row, rather than showing them in different columns, so generally you would expect there to be 3 times the number of rows that I used. There are 5 years of data (2006 to 2010 inclusive) and 20 customers, so my solution needed 100 rows of data (plus header row). If you sort your Sheet3 by Company you can easily see what the difference is - your solution is not showing blanks, so 20 Microns Ltd., which sorts to the top, only has 6 rows instead of 15 rows which the next company, 3I Infotech Ltd., has.

Looking at my solution, there are 42 blank rows, which would equate to 126 of your rows, so instead of you needing 300 rows, you only need (300-126 = 174) rows (plus the header row).

Hope this helps.

Pete

Originally Posted by shweta_ip

... and guide me where am i wrong...

There were two things that needed adjusting. I pointed out that your headings in row 2 of Sheet1 contained spaces, and I removed them in the earlier solution. This time I have left them in and just copied B2:D2 from Sheet1 into C1:E1 of the Desired sheet, so you now have matching headers.

The other thing is that you had amended the formula in C2 to take account of the wider range, but you did not seem to have copied this across and down. Here is the formula:

=IF($A2="","",INDEX(Sheet1!$B$3:$AH$995,MATCH($A2,Sheet1!$A$3:$A$995,0),MATCH(C$1,Sheet1!$B$2:$AH$2,0)+(COUNTIF($A$2:$A2,$A2)-1)*3))

which I have now copied across and down to replace the other formulae.

You have 993 companies and 11 years worth of data, so you will need to copy the formulae down 10923 rows for the complete solution.

Hope this helps.

Pete