Reformatting panel data to make year and state a part of the observation instead of its own column

[yoigottaquestion]

Hi all. I'm using Excel 2007.

Right now I have several panel data sets (each for a single variable) that I want to merge into one big set of multiple variables. Each set has years 2000-2010 as the list of columns, and the set of rows as state. I want to be able to take the sets for variables x, y, and z and put them all into one set of data where the columns include state, year, x, y, and z, and the rows are the ID representing a given state in a given year.

To summarize, I have a table like this for each variable of interest (x,y,z):[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]2000[/TD]
[TD]2001[/TD]
[TD]2002[/TD]
[TD]2003[/TD]
[TD]2004[/TD]
[TD]2005[/TD]
[TD]2006[/TD]
[TD]2007[/TD]
[TD]2008[/TD]
[TD]2009[/TD]
[/TR]
[TR]
[TD]AL[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]GA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FL[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


and I need to make a table of this:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]State[/TD]
[TD]Year[/TD]
[TD]x[/TD]
[TD]y[/TD]
[TD]z[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]GA[/TD]
[TD]2005[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]AL[/TD]
[TD]2007[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]AL[/TD]
[TD]2005[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]FL[/TD]
[TD]2006[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[/TR]
</tbody>[/TABLE]

I'm honestly drawing a blank as I try to reason my way through this, so any help is appreciated.

 

[GlennUK]

Have a look at this, which matches your example, and see if it helps:

<b>Sheet5</b><br /><br /><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td><td >K</td></tr><tr style="height:20px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:10pt; "> </td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:10pt; text-align:right; ">2000</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:10pt; text-align:right; ">2001</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:10pt; text-align:right; ">2002</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:10pt; text-align:right; ">2003</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:10pt; text-align:right; ">2004</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:10pt; text-align:right; ">2005</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:10pt; text-align:right; ">2006</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:10pt; text-align:right; ">2007</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:10pt; text-align:right; ">2008</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:10pt; text-align:right; ">2009</td></tr><tr style="height:20px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:10pt; ">AL</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:10pt; text-align:right; ">315</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:10pt; text-align:right; ">219</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:10pt; text-align:right; ">422</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:10pt; text-align:right; ">181</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:10pt; text-align:right; ">260</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:10pt; text-align:right; ">345</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:10pt; text-align:right; ">154</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:10pt; text-align:right; ">297</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:10pt; text-align:right; ">374</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:10pt; text-align:right; ">280</td></tr><tr style="height:20px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:10pt; ">GA</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:10pt; text-align:right; ">145</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:10pt; text-align:right; ">410</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:10pt; text-align:right; ">460</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:10pt; text-align:right; ">294</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:10pt; text-align:right; ">473</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:10pt; text-align:right; ">209</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:10pt; text-align:right; ">119</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:10pt; text-align:right; ">172</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:10pt; text-align:right; ">239</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:10pt; text-align:right; ">451</td></tr><tr style="height:20px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:10pt; ">FL</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:10pt; text-align:right; ">330</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:10pt; text-align:right; ">386</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:10pt; text-align:right; ">259</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:10pt; text-align:right; ">234</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:10pt; text-align:right; ">419</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:10pt; text-align:right; ">378</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:10pt; text-align:right; ">338</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:10pt; text-align:right; ">211</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:10pt; text-align:right; ">424</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:10pt; text-align:right; ">434</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:20px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:10pt; ">ID</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:10pt; ">State</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:10pt; ">Year</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:10pt; ">x</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:10pt; ">y</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:10pt; ">z</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:20px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:10pt; text-align:right; ">1</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:10pt; ">GA</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:10pt; text-align:right; ">2005</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:10pt; text-align:right; ">209</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:10pt; ">-</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:10pt; ">-</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:20px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:10pt; text-align:right; ">2</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:10pt; ">AL</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:10pt; text-align:right; ">2007</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:10pt; text-align:right; ">297</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:10pt; ">-</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:10pt; ">-</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:20px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:10pt; text-align:right; ">3</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:10pt; ">AL</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:10pt; text-align:right; ">2005</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:10pt; text-align:right; ">345</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:10pt; ">-</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:10pt; ">-</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:20px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:10pt; text-align:right; ">4</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:10pt; ">FL</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:10pt; text-align:right; ">2006</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:10pt; text-align:right; ">338</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:10pt; ">-</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:10pt; ">-</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:20px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:10pt; ">...</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:10pt; ">...</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:10pt; ">...</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:10pt; ">...</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:10pt; ">...</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:10pt; ">...</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Formulas in Table</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >D8</td><td >=INDEX($A$1:$K$4,<span style=' color:008000; '>MATCH($B8,$A$1:$A$4,0)</span>,<span style=' color:008000; '>MATCH($C8,$A$1:$K$1,0)</span>)</td></tr><tr><td >D9</td><td >=INDEX($A$1:$K$4,<span style=' color:008000; '>MATCH($B9,$A$1:$A$4,0)</span>,<span style=' color:008000; '>MATCH($C9,$A$1:$K$1,0)</span>)</td></tr><tr><td >D10</td><td >=INDEX($A$1:$K$4,<span style=' color:008000; '>MATCH($B10,$A$1:$A$4,0)</span>,<span style=' color:008000; '>MATCH($C10,$A$1:$K$1,0)</span>)</td></tr><tr><td >D11</td><td >=INDEX($A$1:$K$4,<span style=' color:008000; '>MATCH($B11,$A$1:$A$4,0)</span>,<span style=' color:008000; '>MATCH($C11,$A$1:$K$1,0)</span>)</td></tr></table></td></tr></table> <br /><br /><span style="font-family:Arial; font-size:9pt; font-weight:bold;background-color:#ffffff; color:#000000; ">Excel Jeannie ( German ) Excel Tabellen im Web darstellen >> </span><a style ="font-family:Arial; font-size:9pt; color:#fcf507; background-color:#800040; font-weight:bold;" href="http://www.excel-jeanie-html.de" target="_blank"> Excel Jeanie HTML 4.8 </a>