Transposing date columns into a tabular format

RichardHill

New Member
Joined
Aug 4, 2010
Messages
5
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
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

prabby25101981

Active Member
Joined
Jul 28, 2010
Messages
348
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

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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
ABCDEF
3Sum of ValueColumn
4Row04/01/201011/01/201018/01/201025/01/2010Grand Total
5Gary Brian Walvin678930
6Joe Rodgers1011121346
7Leon Haddock234514
8Ornela Maric1819202178
9Stephen Robinson1415161762
10Grand Total50556065230
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
ABC
1Page1(All)
3Sum of Value
4RowColumnTotal
5Gary Brian Walvin04/01/20106
611/01/20107
718/01/20108
825/01/20109
9Gary Brian Walvin Total30
10Joe Rodgers04/01/201010
1111/01/201011
1218/01/201012
1325/01/201013
14Joe Rodgers Total46
15Leon Haddock04/01/20102
1611/01/20103
1718/01/20104
1825/01/20105
19Leon Haddock Total14
20Ornela Maric04/01/201018
2111/01/201019
2218/01/201020
2325/01/201021
24Ornela Maric Total78
25Stephen Robinson04/01/201014
2611/01/201015
2718/01/201016
2825/01/201017
29Stephen Robinson Total62
30Grand Total230
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

New Member
Joined
Aug 4, 2010
Messages
5
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

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707

ADVERTISEMENT

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

RichardHill

New Member
Joined
Aug 4, 2010
Messages
5
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

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707

ADVERTISEMENT

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

RichardHill

New Member
Joined
Aug 4, 2010
Messages
5
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

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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

New Member
Joined
Aug 4, 2010
Messages
5
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,057
Messages
5,599,550
Members
414,316
Latest member
ExcelLee

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top