Stephen_IV
Well-known Member
- Joined
- Mar 17, 2003
- Messages
- 1,177
- Office Version
- 365
- 2019
- Platform
- Windows
Good evening I have a sample of data below. I have about 200,000 rows. I am looking for a formula or subroutine that will find the school month and calculate the frequency like below. Here is an example I would like to check Lincoln School in the SEPT column and count how many cells are 1 to 4. In the example it would be 4. Any help would be appreciated.
<style type="text/css">
table.tableizer-table {
font-size: 12px;
border: 1px solid #CCC ;
font-family: Arial, Helvetica, sans-serif;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #CCC ;
}
.tableizer-table th {
background-color: #104E8B ;
color: #FFF ;
font-weight: bold;
}
</style>
<table class="tableizer-table">
<thead><tr class="tableizer-firstrow"><th>STUDENT NUMBER</th><th>SCHOOL</th><th>SCHOOLID</th><th>SEPT</th><th>OCT</th><th>NOV</th><th>DEC</th><th>JAN</th><th>FEB</th><th>MAR</th><th>APR</th><th>MAY</th><th>JUN</th></tr></thead><tbody>
<tr><td>1</td><td>Lincoln School</td><td>111</td><td>13</td><td>12</td><td>3</td><td>1</td><td>1</td><td>2</td><td>4</td><td>6</td><td>8</td><td>2</td></tr>
<tr><td>2</td><td>Madison High School</td><td>333</td><td>9</td><td>5</td><td>4</td><td>11</td><td>8</td><td>5</td><td>2</td><td>1</td><td>2</td><td>0</td></tr>
<tr><td>3</td><td>Madison High School</td><td>333</td><td>4</td><td>6</td><td>3</td><td>0</td><td>8</td><td>7</td><td>4</td><td>3</td><td>3</td><td>1</td></tr>
<tr><td>4</td><td>Lincoln School</td><td>111</td><td>3</td><td>3</td><td>4</td><td>3</td><td>3</td><td>4</td><td>9</td><td>0</td><td>10</td><td>6</td></tr>
<tr><td>5</td><td>Madison High School</td><td>333</td><td>4</td><td>3</td><td>4</td><td>5</td><td>1</td><td>6</td><td>8</td><td>5</td><td>12</td><td>4</td></tr>
<tr><td>6</td><td>Madison High School</td><td>333</td><td>2</td><td>0</td><td>1</td><td>1</td><td>2</td><td>1</td><td>0</td><td>0</td><td>11</td><td>3</td></tr>
<tr><td>7</td><td>Lincoln School</td><td>111</td><td>4</td><td>4</td><td>9</td><td>10</td><td>14</td><td>11</td><td>18</td><td>13</td><td>21</td><td>9</td></tr>
<tr><td>8</td><td>Lincoln School</td><td>111</td><td>7</td><td>1</td><td>2</td><td>4</td><td>4</td><td>15</td><td>20</td><td>14</td><td>19</td><td>6</td></tr>
<tr><td>9</td><td>Johnson High School</td><td>222</td><td>2</td><td>1</td><td>2</td><td>5</td><td>1</td><td>5</td><td>4</td><td>10</td><td>3</td><td>0</td></tr>
<tr><td>10</td><td>Madison High School</td><td>333</td><td>0</td><td>1</td><td>0</td><td>1</td><td>12</td><td>0</td><td>5</td><td>4</td><td>7</td><td>12</td></tr>
<tr><td>11</td><td>Johnson High School</td><td>222</td><td>2</td><td>2</td><td>3</td><td>3</td><td>1</td><td>1</td><td>4</td><td>6</td><td>2</td><td>0</td></tr>
<tr><td>12</td><td>Johnson High School</td><td>222</td><td>3</td><td>1</td><td>3</td><td>1</td><td>3</td><td>5</td><td>8</td><td>6</td><td>12</td><td>7</td></tr>
<tr><td>13</td><td>Madison High School</td><td>333</td><td>8</td><td>7</td><td>4</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td></tr>
<tr><td>14</td><td>Lincoln School</td><td>111</td><td>0</td><td>3</td><td>6</td><td>6</td><td>0</td><td>0</td><td>4</td><td>2</td><td>4</td><td>5</td></tr>
<tr><td>15</td><td>Madison High School</td><td>333</td><td>0</td><td>1</td><td>1</td><td>2</td><td>1</td><td>3</td><td>0</td><td>0</td><td>8</td><td>3</td></tr>
<tr><td>16</td><td>Lincoln School</td><td>111</td><td>1</td><td>1</td><td>1</td><td>8</td><td>7</td><td>4</td><td>0</td><td>0</td><td>1</td><td>2</td></tr>
<tr><td>17</td><td>Madison High School</td><td>333</td><td>2</td><td>2</td><td>2</td><td>4</td><td>1</td><td>1</td><td>3</td><td>2</td><td>2</td><td>2</td></tr>
<tr><td>18</td><td>Johnson High School</td><td>222</td><td>6</td><td>5</td><td>8</td><td>7</td><td>2</td><td>5</td><td>3</td><td>4</td><td>5</td><td>8</td></tr>
<tr><td>19</td><td>Lincoln School</td><td>111</td><td>1</td><td>1</td><td>10</td><td>2</td><td>2</td><td>0</td><td>1</td><td>0</td><td>6</td><td>2</td></tr>
</tbody></table>
This is what I am looking for:
<style type="text/css">
table.tableizer-table {
font-size: 12px;
border: 1px solid #CCC ;
font-family: Arial, Helvetica, sans-serif;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #CCC ;
}
.tableizer-table th {
background-color: #104E8B ;
color: #FFF ;
font-weight: bold;
}
</style>
<table class="tableizer-table">
<thead><tr class="tableizer-firstrow"><th>SCHOOL</th><th> </th><th>1-4</th><th>5-10</th><th>11-15</th><th>16+</th></tr></thead><tbody>
<tr><td>Lincoln School</td><td>SEPT</td><td>4</td><td>1</td><td>1</td><td>0</td></tr>
<tr><td>Lincoln School</td><td>OCT</td><td>6</td><td>0</td><td>1</td><td>0</td></tr>
<tr><td>Lincoln School</td><td>NOV</td><td>4</td><td>3</td><td>0</td><td>0</td></tr>
<tr><td>Lincoln School</td><td>DEC</td><td>4</td><td>3</td><td>0</td><td>0</td></tr>
<tr><td>Lincoln School</td><td>JAN</td><td>4</td><td>1</td><td>1</td><td>0</td></tr>
<tr><td>Lincoln School</td><td>FEB</td><td>3</td><td>0</td><td>2</td><td>0</td></tr>
<tr><td>Lincoln School</td><td>MAR</td><td>3</td><td>1</td><td>0</td><td>2</td></tr>
<tr><td>Lincoln School</td><td>APR</td><td>1</td><td>1</td><td>2</td><td>0</td></tr>
<tr><td>Lincoln School</td><td>MAY</td><td>2</td><td>3</td><td>0</td><td>2</td></tr>
<tr><td>Lincoln School</td><td>JUN</td><td>3</td><td>4</td><td>0</td><td>0</td></tr>
</tbody></table>
<style type="text/css">
table.tableizer-table {
font-size: 12px;
border: 1px solid #CCC ;
font-family: Arial, Helvetica, sans-serif;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #CCC ;
}
.tableizer-table th {
background-color: #104E8B ;
color: #FFF ;
font-weight: bold;
}
</style>
<table class="tableizer-table">
<thead><tr class="tableizer-firstrow"><th>STUDENT NUMBER</th><th>SCHOOL</th><th>SCHOOLID</th><th>SEPT</th><th>OCT</th><th>NOV</th><th>DEC</th><th>JAN</th><th>FEB</th><th>MAR</th><th>APR</th><th>MAY</th><th>JUN</th></tr></thead><tbody>
<tr><td>1</td><td>Lincoln School</td><td>111</td><td>13</td><td>12</td><td>3</td><td>1</td><td>1</td><td>2</td><td>4</td><td>6</td><td>8</td><td>2</td></tr>
<tr><td>2</td><td>Madison High School</td><td>333</td><td>9</td><td>5</td><td>4</td><td>11</td><td>8</td><td>5</td><td>2</td><td>1</td><td>2</td><td>0</td></tr>
<tr><td>3</td><td>Madison High School</td><td>333</td><td>4</td><td>6</td><td>3</td><td>0</td><td>8</td><td>7</td><td>4</td><td>3</td><td>3</td><td>1</td></tr>
<tr><td>4</td><td>Lincoln School</td><td>111</td><td>3</td><td>3</td><td>4</td><td>3</td><td>3</td><td>4</td><td>9</td><td>0</td><td>10</td><td>6</td></tr>
<tr><td>5</td><td>Madison High School</td><td>333</td><td>4</td><td>3</td><td>4</td><td>5</td><td>1</td><td>6</td><td>8</td><td>5</td><td>12</td><td>4</td></tr>
<tr><td>6</td><td>Madison High School</td><td>333</td><td>2</td><td>0</td><td>1</td><td>1</td><td>2</td><td>1</td><td>0</td><td>0</td><td>11</td><td>3</td></tr>
<tr><td>7</td><td>Lincoln School</td><td>111</td><td>4</td><td>4</td><td>9</td><td>10</td><td>14</td><td>11</td><td>18</td><td>13</td><td>21</td><td>9</td></tr>
<tr><td>8</td><td>Lincoln School</td><td>111</td><td>7</td><td>1</td><td>2</td><td>4</td><td>4</td><td>15</td><td>20</td><td>14</td><td>19</td><td>6</td></tr>
<tr><td>9</td><td>Johnson High School</td><td>222</td><td>2</td><td>1</td><td>2</td><td>5</td><td>1</td><td>5</td><td>4</td><td>10</td><td>3</td><td>0</td></tr>
<tr><td>10</td><td>Madison High School</td><td>333</td><td>0</td><td>1</td><td>0</td><td>1</td><td>12</td><td>0</td><td>5</td><td>4</td><td>7</td><td>12</td></tr>
<tr><td>11</td><td>Johnson High School</td><td>222</td><td>2</td><td>2</td><td>3</td><td>3</td><td>1</td><td>1</td><td>4</td><td>6</td><td>2</td><td>0</td></tr>
<tr><td>12</td><td>Johnson High School</td><td>222</td><td>3</td><td>1</td><td>3</td><td>1</td><td>3</td><td>5</td><td>8</td><td>6</td><td>12</td><td>7</td></tr>
<tr><td>13</td><td>Madison High School</td><td>333</td><td>8</td><td>7</td><td>4</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td></tr>
<tr><td>14</td><td>Lincoln School</td><td>111</td><td>0</td><td>3</td><td>6</td><td>6</td><td>0</td><td>0</td><td>4</td><td>2</td><td>4</td><td>5</td></tr>
<tr><td>15</td><td>Madison High School</td><td>333</td><td>0</td><td>1</td><td>1</td><td>2</td><td>1</td><td>3</td><td>0</td><td>0</td><td>8</td><td>3</td></tr>
<tr><td>16</td><td>Lincoln School</td><td>111</td><td>1</td><td>1</td><td>1</td><td>8</td><td>7</td><td>4</td><td>0</td><td>0</td><td>1</td><td>2</td></tr>
<tr><td>17</td><td>Madison High School</td><td>333</td><td>2</td><td>2</td><td>2</td><td>4</td><td>1</td><td>1</td><td>3</td><td>2</td><td>2</td><td>2</td></tr>
<tr><td>18</td><td>Johnson High School</td><td>222</td><td>6</td><td>5</td><td>8</td><td>7</td><td>2</td><td>5</td><td>3</td><td>4</td><td>5</td><td>8</td></tr>
<tr><td>19</td><td>Lincoln School</td><td>111</td><td>1</td><td>1</td><td>10</td><td>2</td><td>2</td><td>0</td><td>1</td><td>0</td><td>6</td><td>2</td></tr>
</tbody></table>
This is what I am looking for:
<style type="text/css">
table.tableizer-table {
font-size: 12px;
border: 1px solid #CCC ;
font-family: Arial, Helvetica, sans-serif;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #CCC ;
}
.tableizer-table th {
background-color: #104E8B ;
color: #FFF ;
font-weight: bold;
}
</style>
<table class="tableizer-table">
<thead><tr class="tableizer-firstrow"><th>SCHOOL</th><th> </th><th>1-4</th><th>5-10</th><th>11-15</th><th>16+</th></tr></thead><tbody>
<tr><td>Lincoln School</td><td>SEPT</td><td>4</td><td>1</td><td>1</td><td>0</td></tr>
<tr><td>Lincoln School</td><td>OCT</td><td>6</td><td>0</td><td>1</td><td>0</td></tr>
<tr><td>Lincoln School</td><td>NOV</td><td>4</td><td>3</td><td>0</td><td>0</td></tr>
<tr><td>Lincoln School</td><td>DEC</td><td>4</td><td>3</td><td>0</td><td>0</td></tr>
<tr><td>Lincoln School</td><td>JAN</td><td>4</td><td>1</td><td>1</td><td>0</td></tr>
<tr><td>Lincoln School</td><td>FEB</td><td>3</td><td>0</td><td>2</td><td>0</td></tr>
<tr><td>Lincoln School</td><td>MAR</td><td>3</td><td>1</td><td>0</td><td>2</td></tr>
<tr><td>Lincoln School</td><td>APR</td><td>1</td><td>1</td><td>2</td><td>0</td></tr>
<tr><td>Lincoln School</td><td>MAY</td><td>2</td><td>3</td><td>0</td><td>2</td></tr>
<tr><td>Lincoln School</td><td>JUN</td><td>3</td><td>4</td><td>0</td><td>0</td></tr>
</tbody></table>