VBA 10th Line of data to Another Sheet

[Stephen_IV]

Good afternoon,

I have a sample data set below.I have over 500,000 records. Each child can have from 1 to 182 days absent (1 to 182 lines of data). What I need to do is to have VBA list on Sheet2 every 10th school or 10th line per Id number and if the child does not have 10 absences then bring back 0. Thanks in advance!

For the sample below:
11111 10th line is 102
22222 10th line is 114

Thanks in advance
<style type="text/css">
table.tableizer-table {
font-size: 8px;
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>ID</th><th>School</th><th>Date</th><th>DESCRIPTION</th></tr></thead><tbody>
<tr><td>11111</td><td>100</td><td>9/1/2016</td><td>Absent</td></tr>
<tr><td>11111</td><td>100</td><td>9/2/2016</td><td>Unexcused Absence</td></tr>
<tr><td>11111</td><td>100</td><td>9/5/2016</td><td>Absent</td></tr>
<tr><td>11111</td><td>100</td><td>9/6/2016</td><td>Absent</td></tr>
<tr><td>11111</td><td>100</td><td>9/7/2016</td><td>Absent</td></tr>
<tr><td>11111</td><td>100</td><td>9/8/2016</td><td>Absent</td></tr>
<tr><td>11111</td><td>100</td><td>9/9/2016</td><td>Absent</td></tr>
<tr><td>11111</td><td>100</td><td>9/12/2016</td><td>Absent</td></tr>
<tr><td>11111</td><td>100</td><td>9/13/2016</td><td>Absent</td></tr>
<tr><td>11111</td><td>102</td><td>9/14/2016</td><td>Absent</td></tr>
<tr><td>11111</td><td>102</td><td>9/15/2016</td><td>Absent</td></tr>
<tr><td>11111</td><td>103</td><td>9/16/2016</td><td>Absent</td></tr>
<tr><td>11111</td><td>103</td><td>9/19/2016</td><td>Absent</td></tr>
<tr><td>22222</td><td>102</td><td>9/20/2016</td><td>Absent</td></tr>
<tr><td>22222</td><td>102</td><td>9/21/2016</td><td>Unexcused Absence</td></tr>
<tr><td>22222</td><td>102</td><td>9/22/2016</td><td>Absent</td></tr>
<tr><td>22222</td><td>102</td><td>9/23/2016</td><td>Absent</td></tr>
<tr><td>22222</td><td>102</td><td>9/26/2016</td><td>Unexcused Absence</td></tr>
<tr><td>22222</td><td>102</td><td>9/27/2016</td><td>Absent</td></tr>
<tr><td>22222</td><td>102</td><td>9/28/2016</td><td>Absent</td></tr>
<tr><td>22222</td><td>102</td><td>9/29/2016</td><td>Absent</td></tr>
<tr><td>22222</td><td>102</td><td>9/30/2016</td><td>Unexcused Absence</td></tr>
<tr><td>22222</td><td>114</td><td>10/3/2016</td><td>Absent</td></tr>
<tr><td>22222</td><td>114</td><td>10/4/2016</td><td>Absent</td></tr>
<tr><td>22222</td><td>116</td><td>10/5/2016</td><td>Unexcused Absence</td></tr>
</tbody></table>

 

[shg]

If you are amenable to an alternative, you could add a column that counts absences:

	A​	B​	C​	D​	E​	F​
1​	ID​	School​	Date​	DESCRIPTION​	Count​	​
2​	11111​	100​	9/1/2016​	Absent	1​	E2: =COUNTIF(INDEX(A:A, MAX(2, ROW() - 182)):A2, A2)
3​	11111​	100​	9/2/2016​	Unexcused Absence	2​
4​	11111​	100​	9/5/2016​	Absent	3​
5​	11111​	100​	9/6/2016​	Absent	4​
6​	11111​	100​	9/7/2016​	Absent	5​
7​	11111​	100​	9/8/2016​	Absent	6​
8​	11111​	100​	9/9/2016​	Absent	7​
9​	11111​	100​	9/12/2016​	Absent	8​
10​	11111​	100​	9/13/2016​	Absent	9​
11​	11111​	102​	9/14/2016​	Absent	10​
12​	11111​	102​	9/15/2016​	Absent	11​
13​	11111​	103​	9/16/2016​	Absent	12​
14​	11111​	103​	9/19/2016​	Absent	13​
15​	22222​	102​	9/20/2016​	Absent	1​
16​	22222​	102​	9/21/2016​	Unexcused Absence	2​
17​	22222​	102​	9/22/2016​	Absent	3​
18​	22222​	102​	9/23/2016​	Absent	4​
19​	22222​	102​	9/26/2016​	Unexcused Absence	5​
20​	22222​	102​	9/27/2016​	Absent	6​
21​	22222​	102​	9/28/2016​	Absent	7​
22​	22222​	102​	9/29/2016​	Absent	8​
23​	22222​	102​	9/30/2016​	Unexcused Absence	9​
24​	22222​	114​	10/3/2016​	Absent	10​
25​	22222​	114​	10/4/2016​	Absent	11​
26​	22222​	116​	10/5/2016​	Unexcused Absence	12​

The formula is a little exotic sou it doesn't have to look at 500,000 rows, and it requires that the table be sorted by ID (as it is now). Then you can filter on that column for >= 10:

	A​	B​	C​	D​	E​
1​	ID​	School​	Date​	DESCRIPTION​	Count​
11​	11111​	102​	9/14/2016​	Absent	10​
12​	11111​	102​	9/15/2016​	Absent	11​
13​	11111​	103​	9/16/2016​	Absent	12​
14​	11111​	103​	9/19/2016​	Absent	13​
24​	22222​	114​	10/3/2016​	Absent	10​
25​	22222​	114​	10/4/2016​	Absent	11​
26​	22222​	116​	10/5/2016​	Unexcused Absence	12​

 

[Stephen_IV]

shg,

Love the formula! I can make it work. I'm surprised how fast it is! Thank you again for your help and guidance!

 

[shg]

I'm pleased it will work for you, you're welcome.

 

[shg]

Doh, that was a needlessly complicated formula. Assuming there are no blank rows, and the data is sorted by ID, then

	A​	B​	C​	D​	E​	F​
1​	ID​	School​	Date​	DESCRIPTION​	Count​	​
2​	11111​	100​	9/1/2016​	Absent	1​	E2: =IF(A2=A1, E1+1, 1)
3​	11111​	100​	9/2/2016​	Unexcused Absence	2​
4​	11111​	100​	9/5/2016​	Absent	3​
5​	11111​	100​	9/6/2016​	Absent	4​
6​	11111​	100​	9/7/2016​	Absent	5​

... is all you need.