Transposing date columns into a tabular format

[RichardHill]

I have a Excel 2007 spreadsheet of data as follows:
<TABLE style="WIDTH: 280pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=373 border=0><COLGROUP><COL style="WIDTH: 88pt; mso-width-source: userset; mso-width-alt: 4278" width=117><COL style="WIDTH: 48pt" span=4 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff 0.5pt solid; BORDER-LEFT: #ccccff 0.5pt solid; WIDTH: 88pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: #666699" width=117 height=20>PV ResourceName</TD><TD class=xl64 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff 0.5pt solid; BORDER-LEFT: #ccccff; WIDTH: 48pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: #666699" width=64>04-Jan</TD><TD class=xl64 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff 0.5pt solid; BORDER-LEFT: #ccccff; WIDTH: 48pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: #666699" width=64>11-Jan</TD><TD class=xl64 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff 0.5pt solid; BORDER-LEFT: #ccccff; WIDTH: 48pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: #666699" width=64>18-Jan</TD><TD class=xl64 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff 0.5pt solid; BORDER-LEFT: #ccccff; WIDTH: 48pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: #666699" width=64>25-Jan</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff 0.5pt solid; BORDER-LEFT: #ccccff 0.5pt solid; WIDTH: 88pt; BORDER-BOTTOM: #ccccff 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: white" width=117 height=20>Leon Haddock</TD><TD class=xl65 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 48pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" width=64>2</TD><TD class=xl65 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 48pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" width=64>3</TD><TD class=xl65 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 48pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" width=64>4</TD><TD class=xl65 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 48pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" width=64>5</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff 0.5pt solid; WIDTH: 88pt; BORDER-BOTTOM: #ccccff 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: white" width=117 height=20>Gary Brian Walvin</TD><TD class=xl65 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 48pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" width=64>6</TD><TD class=xl65 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 48pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" width=64>7</TD><TD class=xl65 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 48pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" width=64>8</TD><TD class=xl65 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 48pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" width=64>9</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff 0.5pt solid; WIDTH: 88pt; BORDER-BOTTOM: #ccccff 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: white" width=117 height=20>Joe Rodgers</TD><TD class=xl65 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 48pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" width=64>10</TD><TD class=xl65 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 48pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" width=64>11</TD><TD class=xl65 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 48pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" width=64>12</TD><TD class=xl65 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 48pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" width=64>13</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff 0.5pt solid; WIDTH: 88pt; BORDER-BOTTOM: #ccccff 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: white" width=117 height=20>Stephen Robinson</TD><TD class=xl65 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 48pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" width=64>14</TD><TD class=xl65 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 48pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" width=64>15</TD><TD class=xl65 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 48pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" width=64>16</TD><TD class=xl65 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 48pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" width=64>17</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff 0.5pt solid; WIDTH: 88pt; BORDER-BOTTOM: #ccccff 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: white" width=117 height=20>Ornela Maric</TD><TD class=xl65 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 48pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" width=64>18</TD><TD class=xl65 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 48pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" width=64>19</TD><TD class=xl65 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 48pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" width=64>20</TD><TD class=xl65 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 48pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" width=64>21</TD></TR></TBODY></TABLE>

and I want to create it in the following format so that I can manipulate it using a Pivot Table:
<TABLE style="WIDTH: 184pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=245 border=0><COLGROUP><COL style="WIDTH: 88pt; mso-width-source: userset; mso-width-alt: 4278" width=117><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff 0.5pt solid; BORDER-LEFT: #ccccff 0.5pt solid; WIDTH: 88pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: #666699" width=117 height=20>Resource</TD><TD class=xl66 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff 0.5pt solid; BORDER-LEFT: #ccccff; WIDTH: 48pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: #666699" width=64>Week</TD><TD class=xl66 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff 0.5pt solid; BORDER-LEFT: #ccccff; WIDTH: 48pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: #666699" width=64>Hours</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff 0.5pt solid; BORDER-LEFT: #ccccff 0.5pt solid; WIDTH: 88pt; BORDER-BOTTOM: #ccccff 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: white" width=117 height=20>Leon Haddock</TD><TD class=xl68 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 48pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" width=64>04-Jan-10</TD><TD class=xl67 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 48pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" width=64>2</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff 0.5pt solid; WIDTH: 88pt; BORDER-BOTTOM: #ccccff 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: white" width=117 height=20>Leon Haddock</TD><TD class=xl68 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 48pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" width=64>11-Jan-10</TD><TD class=xl67 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 48pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" width=64>3</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff 0.5pt solid; WIDTH: 88pt; BORDER-BOTTOM: #ccccff 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: white" width=117 height=20>Leon Haddock</TD><TD class=xl68 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 48pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" width=64>18-Jan-10</TD><TD class=xl67 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 48pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" width=64>4</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff 0.5pt solid; WIDTH: 88pt; BORDER-BOTTOM: #ccccff 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: white" width=117 height=20>Leon Haddock</TD><TD class=xl68 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 48pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" width=64>25-Jan-10</TD><TD class=xl67 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 48pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" width=64>5</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff 0.5pt solid; WIDTH: 88pt; BORDER-BOTTOM: #ccccff 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: white" width=117 height=20>Gary Brian Walvin</TD><TD class=xl68 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 48pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" width=64>04-Jan-10</TD><TD class=xl67 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 48pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" width=64>6</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff 0.5pt solid; WIDTH: 88pt; BORDER-BOTTOM: #ccccff 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: white" width=117 height=20>Gary Brian Walvin</TD><TD class=xl68 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 48pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" width=64>11-Jan-10</TD><TD class=xl67 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 48pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" width=64>7</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff 0.5pt solid; WIDTH: 88pt; BORDER-BOTTOM: #ccccff 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: white" width=117 height=20>Gary Brian Walvin</TD><TD class=xl68 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 48pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" width=64>18-Jan-10</TD><TD class=xl67 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 48pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" width=64>8</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff 0.5pt solid; WIDTH: 88pt; BORDER-BOTTOM: #ccccff 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: white" width=117 height=20>Gary Brian Walvin</TD><TD class=xl68 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 48pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" width=64>25-Jan-10</TD><TD class=xl67 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 48pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" width=64>9</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff 0.5pt solid; WIDTH: 88pt; BORDER-BOTTOM: #ccccff 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: white" width=117 height=20>Joe Rodgers</TD><TD class=xl68 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 48pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" width=64>04-Jan-10</TD><TD class=xl67 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 48pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" width=64>10</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff 0.5pt solid; WIDTH: 88pt; BORDER-BOTTOM: #ccccff 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: white" width=117 height=20>Joe Rodgers</TD><TD class=xl68 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 48pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" width=64>11-Jan-10</TD><TD class=xl67 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 48pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" width=64>11</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff 0.5pt solid; WIDTH: 88pt; BORDER-BOTTOM: #ccccff 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: white" width=117 height=20>Joe Rodgers</TD><TD class=xl68 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 48pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" width=64>18-Jan-10</TD><TD class=xl67 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 48pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" width=64>12</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff 0.5pt solid; WIDTH: 88pt; BORDER-BOTTOM: #ccccff 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: white" width=117 height=20>Joe Rodgers</TD><TD class=xl68 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 48pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" width=64>25-Jan-10</TD><TD class=xl67 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 48pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" width=64>13</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff 0.5pt solid; WIDTH: 88pt; BORDER-BOTTOM: #ccccff 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: white" width=117 height=20>Stephen Robinson</TD><TD class=xl68 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 48pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" width=64>04-Jan-10</TD><TD class=xl67 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 48pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" width=64>14</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff 0.5pt solid; WIDTH: 88pt; BORDER-BOTTOM: #ccccff 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: white" width=117 height=20>Stephen Robinson</TD><TD class=xl68 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 48pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" width=64>11-Jan-10</TD><TD class=xl67 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 48pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" width=64>15</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff 0.5pt solid; WIDTH: 88pt; BORDER-BOTTOM: #ccccff 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: white" width=117 height=20>Stephen Robinson</TD><TD class=xl68 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 48pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" width=64>18-Jan-10</TD><TD class=xl67 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 48pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" width=64>16</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff 0.5pt solid; WIDTH: 88pt; BORDER-BOTTOM: #ccccff 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: white" width=117 height=20>Stephen Robinson</TD><TD class=xl68 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 48pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" width=64>25-Jan-10</TD><TD class=xl67 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 48pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" width=64>17</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff 0.5pt solid; WIDTH: 88pt; BORDER-BOTTOM: #ccccff 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: white" width=117 height=20>Ornela Maric</TD><TD class=xl68 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 48pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" width=64>04-Jan-10</TD><TD class=xl67 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 48pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" width=64>18</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff 0.5pt solid; WIDTH: 88pt; BORDER-BOTTOM: #ccccff 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: white" width=117 height=20>Ornela Maric</TD><TD class=xl68 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 48pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" width=64>11-Jan-10</TD><TD class=xl67 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 48pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" width=64>19</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff 0.5pt solid; WIDTH: 88pt; BORDER-BOTTOM: #ccccff 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: white" width=117 height=20>Ornela Maric</TD><TD class=xl68 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 48pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" width=64>18-Jan-10</TD><TD class=xl67 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 48pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" width=64>20</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff 0.5pt solid; WIDTH: 88pt; BORDER-BOTTOM: #ccccff 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: white" width=117 height=20>Ornela Maric</TD><TD class=xl68 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 48pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" width=64>25-Jan-10</TD><TD class=xl67 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 48pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" width=64>21</TD></TR></TBODY></TABLE>

My original data has some 800 rows for staff and 52 data columns (one for each week). How can I create the tabular layout in Excel 2007? BTW, I'm not allwoed to dowload and install other tools on my desktop so I am limited to using Excel

 

[prabby25101981]

Try using Pivot Tables... You need to keep the PV ResourceName in Rows and All dates in data fields with SUM activated for all...

 

[Richard Schollar]

Hi Richard

Follow these steps precisely:

1. with this sheet visible in Excel open up the pivot table wizard using Alt+D then P
2. Select Mutliple Consoliodation Ranges and click Next
3. Select top page options >Next
4. Select your range and click Add (i know you only have one range)
5. Select a location for the pivot table>Finish
6. This will create a bare pivot table with "Sum of value" button, "Row" button and "Column" button:

Excel Workbook
	A	B	C	D	E	F
3	Sum of Value	Column
4	Row	04/01/2010	11/01/2010	18/01/2010	25/01/2010	Grand Total
5	Gary Brian Walvin	6	7	8	9	30
6	Joe Rodgers	10	11	12	13	46
7	Leon Haddock	2	3	4	5	14
8	Ornela Maric	18	19	20	21	78
9	Stephen Robinson	14	15	16	17	62
10	Grand Total	50	55	60	65	230
Sheet3

Excel 2002
Left-click on the "Column" button and drag it on to one of the names under the Row button - this should be the result:

Excel Workbook
	A	B	C
1	Page1	(All)
3	Sum of Value
4	Row	Column	Total
5	Gary Brian Walvin	04/01/2010	6
6		11/01/2010	7
7		18/01/2010	8
8		25/01/2010	9
9	Gary Brian Walvin Total		30
10	Joe Rodgers	04/01/2010	10
11		11/01/2010	11
12		18/01/2010	12
13		25/01/2010	13
14	Joe Rodgers Total		46
15	Leon Haddock	04/01/2010	2
16		11/01/2010	3
17		18/01/2010	4
18		25/01/2010	5
19	Leon Haddock Total		14
20	Ornela Maric	04/01/2010	18
21		11/01/2010	19
22		18/01/2010	20
23		25/01/2010	21
24	Ornela Maric Total		78
25	Stephen Robinson	04/01/2010	14
26		11/01/2010	15
27		18/01/2010	16
28		25/01/2010	17
29	Stephen Robinson Total		62
30	Grand Total		230
Sheet3

Excel 2002

You have the essentials of your renormalised table. next, right-click on one of the Row data labels (eg cell A5 in the above), choose Field Settings and select None for Subtotals>Ok.

7. Select all the cells on the sheet and go Ctrl+C to copy and then Edit>PasteSpecial>Values to fix the values (you lose the pivot table this way, but it's done its job).

8. Select all of column A from the top name to the Grand Total cell at the bottom and hit F5 and click the Special Button. Select blanks in the dialog>OK
9. Immediately type = and press the Up arrow key and confirm with Ctrl+Enter (not just Enter). This will insert formulas into all the blank cells. Then immediately copy and pasteSpecial>Values this column to fix them.


You now have your data table and you just need to append field names to it.

 

[RichardHill]

Thanks, Richard. I can follow all of what you say apart from the "Left-click on the Column button and drag it on to one of the names under the Row button". If I left-click on cell B3, then drag to A6 for example, it selects all the cells I am dragging the mouse over - A3:B6.

Richard

 

[Richard Schollar]

Sorry, should have made clear - click and hold down the left mouse button and drag

 

[RichardHill]

Sorry, I must be dense today. If you click and hold down then drag, surely it selects all the cells you are dragging over? Or are you saying taht I should drag the Columns button in the Pivot table Field List to below the Rows button in the same list?

 

[Richard Schollar]

Yes - that's what i meant (Click and drag Columns button)

 

[RichardHill]

Getting there! I end up with:

<table style="border-collapse: collapse; width: 143pt;" border="0" cellpadding="0" cellspacing="0" width="190"><col style="width: 76pt;" width="101"> <col style="width: 67pt;" width="89"> <tbody><tr style="height: 15pt;" height="20"> <td style="border-style: none none solid; border-color: -moz-use-text-color -moz-use-text-color rgb(149, 179, 215); border-width: medium medium 0.5pt; background: rgb(219, 229, 241) none repeat scroll 0% 0%; height: 15pt; width: 76pt; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;" height="20" width="101">Page1</td> <td style="border-style: none none solid; border-color: -moz-use-text-color -moz-use-text-color rgb(149, 179, 215); border-width: medium medium 0.5pt; background: rgb(219, 229, 241) none repeat scroll 0% 0%; width: 67pt; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;" width="89">(All)</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="border-style: none none solid; border-color: -moz-use-text-color -moz-use-text-color rgb(149, 179, 215); border-width: medium medium 0.5pt; background: rgb(219, 229, 241) none repeat scroll 0% 0%; height: 15pt; font-size: 11pt; color: black; font-weight: 700; text-decoration: none; font-family: Calibri; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;" height="20">Row Labels</td> <td style="border-style: none none solid; border-color: -moz-use-text-color -moz-use-text-color rgb(149, 179, 215); border-width: medium medium 0.5pt; background: rgb(219, 229, 241) none repeat scroll 0% 0%; font-size: 11pt; color: black; font-weight: 700; text-decoration: none; font-family: Calibri; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;">Sum of Value</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="border-style: none none solid; border-color: -moz-use-text-color -moz-use-text-color rgb(149, 179, 215); border-width: medium medium 0.5pt; height: 15pt; font-size: 11pt; color: black; font-weight: 700; text-decoration: none; font-family: Calibri;" height="20">Davies</td> <td style="border-style: none none solid; border-color: -moz-use-text-color -moz-use-text-color rgb(149, 179, 215); border-width: medium medium 0.5pt; font-size: 11pt; color: black; font-weight: 700; text-decoration: none; font-family: Calibri;">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">04/01/2010</td> <td align="right">738</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">11/01/2010</td> <td align="right">937</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">18/01/2010</td> <td align="right">634</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">25/01/2010</td> <td align="right">956</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="border-style: none none solid; border-color: -moz-use-text-color -moz-use-text-color rgb(149, 179, 215); border-width: medium medium 0.5pt; height: 15pt; font-size: 11pt; color: black; font-weight: 700; text-decoration: none; font-family: Calibri;" height="20">Jones</td> <td style="border-style: none none solid; border-color: -moz-use-text-color -moz-use-text-color rgb(149, 179, 215); border-width: medium medium 0.5pt; font-size: 11pt; color: black; font-weight: 700; text-decoration: none; font-family: Calibri;">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">04/01/2010</td> <td align="right">156</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">11/01/2010</td> <td align="right">374</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">18/01/2010</td> <td align="right">279</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">25/01/2010</td> <td align="right">483</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="border-style: none none solid; border-color: -moz-use-text-color -moz-use-text-color rgb(149, 179, 215); border-width: medium medium 0.5pt; height: 15pt; font-size: 11pt; color: black; font-weight: 700; text-decoration: none; font-family: Calibri;" height="20">Smith</td> <td style="border-style: none none solid; border-color: -moz-use-text-color -moz-use-text-color rgb(149, 179, 215); border-width: medium medium 0.5pt; font-size: 11pt; color: black; font-weight: 700; text-decoration: none; font-family: Calibri;">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">04/01/2010</td> <td align="right">12</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">11/01/2010</td> <td align="right">134</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">18/01/2010</td> <td align="right">56</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">25/01/2010</td> <td align="right">254</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="border-style: solid none none; border-color: rgb(149, 179, 215) -moz-use-text-color -moz-use-text-color; border-width: 0.5pt medium medium; background: rgb(219, 229, 241) none repeat scroll 0% 0%; height: 15pt; font-size: 11pt; color: black; font-weight: 700; text-decoration: none; font-family: Calibri; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;" height="20">Grand Total</td> <td style="border-style: solid none none; border-color: rgb(149, 179, 215) -moz-use-text-color -moz-use-text-color; border-width: 0.5pt medium medium; background: rgb(219, 229, 241) none repeat scroll 0% 0%; font-size: 11pt; color: black; font-weight: 700; text-decoration: none; font-family: Calibri; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;" align="right">5013</td> </tr> </tbody></table>
so the dates are below the names in column A rather than in Column B as your example showed.

 

[Richard Schollar]

I'm not sure how you managed that because try as i might, I can't get the column labels to go directly below the row labels - this might be a formatting problem in xl2007 (i am using 2002). You need the appearance to be based on the old pivot table format - I believe it's in Pivot Table Options as Classic Pivot Table Layout.

 

[RichardHill]

Sorted! Thank you so much. I changed the setting on the Display tab on Pivot Table Options to Clasic Pivot Table Layout and it all works