Identify Every 7th Row

[WONGMEISTER]

I have a list of records in an Excel. I'm trying to create a column that will identify every 7th record. I have a column named "RecNo" that begins at 1 and goes all the way to 20,000 that I can use in my formula.

Thanks.

 

[T. Valko]

I have a list of records in an Excel. I'm trying to create a column that will identify every 7th record. I have a column named "RecNo" that begins at 1 and goes all the way to 20,000 that I can use in my formula.

Thanks.

Do you mean you want to EXTRACT every 7th record?

How about giving us the first few cell addresses of the records to extract so we can see the pattern.

Also tell us where you want the records extracted to.

 

[WONGMEISTER]

Yes. Sorry, I meant extract.

My data consists of three columns: [RecNo], [File Name] and [DOR]. Row 1 contains these headings. The data begins at A2 and continues to A20000. The value of A1 is 1, A3 is 2, A4 is 3, A5 is 4, etc.

Thanks.

 

[MAP1168]

One way to do it would be to create another column and the value in the first cell of this column would be 7. Then in the next cell down add 7 to the first cell then in the next cell down add 7 to that result, etc etc. So that you have something like this.

<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=64><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 width=64 align=right>7</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>14</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>21</TD></TR>

<TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" id=td_post_2850996 height=20 align=right>28</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>35</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>42</TD></TR>

<TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>49</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>56</TD></TR></TBODY></TABLE>

use a lookup function =VLOOKUP(D1,$A$1:$B$8,2,FALSE)

substitute the beginning of your table in a1 and the end or your table for b8. This is your lookup array, Note how many columns it has. In my example there are 2. So when it comes to the value 7 in my lookup value finds 7 in the lookup array it will give me whatever's in the second column.

Your rec# must be the first column of your lookup array. The value False is an optional parameter that tells the lookup function you only want the values for 7,14,21,28 etc. Otherwise you might get values you're not looking for.

 

[T. Valko]

Yes. Sorry, I meant extract.

My data consists of three columns: [RecNo], [File Name] and [DOR]. Row 1 contains these headings. The data begins at A2 and continues to A20000. The value of A1 is 1, A3 is 2, A4 is 3, A5 is 4, etc.

Thanks.

Let's try this...

Suppose this is your data:

Book1
	A
1	Column Header
2	Data
3	Data
4	Data
5	Data
6	Data
7	Data
8	Data
9	Data
10	Data
11	Data
12	Data
13	Data
14	Data
15	Data
16	Data
17	Data
18	Data
19	Data
20	Data
21	Data
22	Data
23	Data
24	Data
25	Data
26	Data
27	Data
28	Data
29	Data
30	Data
31	Data
32	Data
33	Data
34	Data
35	Data
Sheet1

Tell us which cells you want to extract and where you want it to appear.

 

[WONGMEISTER]

Thanks Map and TValko.

I think I got it working.

 

[Trouttrap2]

I know you have the solution now, but I thought i'd give you my idea.
Enter this in cell B1. It will extract every 7th record to that cell.

=IF(MOD(ROW(),7)=0,A1,"")

 

[T. Valko]

Thanks Map and TValko.

I think I got it working.

Good deal. Thanks for letting us know!

 

[WONGMEISTER]

Trouttrap...

That solutions works also....short and sweeter!!!

Thanks to you and the others.