Select every Nth cell in column? Help please

3 columns A2:C100 contains data.  In E2:E14, I want a formula or 
function that will get every 3rd cell from Column A, F2:F14 every 3rd 
cell from Col B, G2:G14 every 3rdcell from Col C  E.g.:

A	B	C	D	E	F	G
Days	Week	Mon		RDays	RWeek	RMon
1	2	3		7	8	9
4	5	6		4	5	6
7	8	9		9	8	7
10	110	12		3	3	3
1	2	3
4	5	6
7	8	9
12	11	10
9	8	7
6	5	4	
3	2	1
3	3	3

I'm guessing Vlookup somehow, but I sure cant figure out how to do it. 
Appreciate any help you can provide.  Basically, I'm just trying to get 
a smaller set of data to work with in Cols E:G versus using the data in 
Col A:C which currently is over 5000 rows.  So I want to be able to get 
every 20, 30 or 50 numbers.  Hope I explained it good enough so that you 
understand what I'm needing.  Thanks again.

Ed
0
abqhusker (8)
4/8/2006 2:25:59 AM
excel 39879 articles. 2 followers. Follow

5 Replies
503 Views

Similar Articles

[PageSpeed] 29

=INDEX($A$2:$C$1100,ROWS($A$2:$A2)*3,COLUMNS($A$2:A$2))

copy across 3 cells and then copy down will give you your values

-- 

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon




"abqhusker" <abqhusker@hotmail.com> wrote in message 
news:44371e36$0$27879$ed362ca5@nr2.newsreader.com...
>3 columns A2:C100 contains data.  In E2:E14, I want a formula or function 
>that will get every 3rd cell from Column A, F2:F14 every 3rd cell from Col 
>B, G2:G14 every 3rdcell from Col C  E.g.:
>
> A B C D E F G
> Days Week Mon RDays RWeek RMon
> 1 2 3 7 8 9
> 4 5 6 4 5 6
> 7 8 9 9 8 7
> 10 110 12 3 3 3
> 1 2 3
> 4 5 6
> 7 8 9
> 12 11 10
> 9 8 7
> 6 5 4 3 2 1
> 3 3 3
>
> I'm guessing Vlookup somehow, but I sure cant figure out how to do it. 
> Appreciate any help you can provide.  Basically, I'm just trying to get a 
> smaller set of data to work with in Cols E:G versus using the data in Col 
> A:C which currently is over 5000 rows.  So I want to be able to get every 
> 20, 30 or 50 numbers.  Hope I explained it good enough so that you 
> understand what I'm needing.  Thanks again.
>
> Ed 

0
Peo
4/8/2006 3:42:11 AM
Oops! Change $C$1100 to $C$100

-- 

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon




"Peo Sjoblom" <peo.sjoblom@^^nwexcelsolutions.com> wrote in message 
news:O992u5rWGHA.4924@TK2MSFTNGP05.phx.gbl...
> =INDEX($A$2:$C$1100,ROWS($A$2:$A2)*3,COLUMNS($A$2:A$2))
>
> copy across 3 cells and then copy down will give you your values
>
> -- 
>
> Regards,
>
> Peo Sjoblom
>
> Northwest Excel Solutions
>
> www.nwexcelsolutions.com
>
> (remove ^^ from email address)
>
> Portland, Oregon
>
>
>
>
> "abqhusker" <abqhusker@hotmail.com> wrote in message 
> news:44371e36$0$27879$ed362ca5@nr2.newsreader.com...
>>3 columns A2:C100 contains data.  In E2:E14, I want a formula or function 
>>that will get every 3rd cell from Column A, F2:F14 every 3rd cell from Col 
>>B, G2:G14 every 3rdcell from Col C  E.g.:
>>
>> A B C D E F G
>> Days Week Mon RDays RWeek RMon
>> 1 2 3 7 8 9
>> 4 5 6 4 5 6
>> 7 8 9 9 8 7
>> 10 110 12 3 3 3
>> 1 2 3
>> 4 5 6
>> 7 8 9
>> 12 11 10
>> 9 8 7
>> 6 5 4 3 2 1
>> 3 3 3
>>
>> I'm guessing Vlookup somehow, but I sure cant figure out how to do it. 
>> Appreciate any help you can provide.  Basically, I'm just trying to get a 
>> smaller set of data to work with in Cols E:G versus using the data in Col 
>> A:C which currently is over 5000 rows.  So I want to be able to get every 
>> 20, 30 or 50 numbers.  Hope I explained it good enough so that you 
>> understand what I'm needing.  Thanks again.
>>
>> Ed
> 

0
Peo
4/8/2006 3:53:18 AM
Peo Sjoblom wrote:
> =INDEX($A$2:$C$1100,ROWS($A$2:$A2)*3,COLUMNS($A$2:A$2))
> 
> copy across 3 cells and then copy down will give you your values
> 
That works fine; however, I am wondering if there is a way to do the 
same thing without always having to include/select the 5000+ row range. 
  If not that's fine, this formula does exactly what I wanted.  I wish I 
understood what Index, Rows, and Columns function do and how this 
formula works but I'm happy it works regardless of whether I understand 
it or not.  Thanks for answering right away.  You guys are awesome. 
Nice web site by the way.

Ed
0
abqhusker (8)
4/8/2006 11:19:01 PM
You could use this

=OFFSET($A$2,ROWS($A$2:$A2)*3-1,COLUMNS($A$2:A$2)-1)

where you just have to put in the first upper left cell, however that 
formula is volatile and if you have a lot of these formulas it might slow 
down the performance

-- 

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon




"abqhusker" <abqhusker@hotmail.com> wrote in message 
news:443843e0$0$27868$ed362ca5@nr2.newsreader.com...
> Peo Sjoblom wrote:
>> =INDEX($A$2:$C$1100,ROWS($A$2:$A2)*3,COLUMNS($A$2:A$2))
>>
>> copy across 3 cells and then copy down will give you your values
>>
> That works fine; however, I am wondering if there is a way to do the same 
> thing without always having to include/select the 5000+ row range. If not 
> that's fine, this formula does exactly what I wanted.  I wish I understood 
> what Index, Rows, and Columns function do and how this formula works but 
> I'm happy it works regardless of whether I understand it or not.  Thanks 
> for answering right away.  You guys are awesome. Nice web site by the way.
>
> Ed 

0
Peo
4/9/2006 5:55:05 AM
Peo Sjoblom wrote:
> You could use this
> 
> =OFFSET($A$2,ROWS($A$2:$A2)*3-1,COLUMNS($A$2:A$2)-1)
> 
> where you just have to put in the first upper left cell, however that 
> formula is volatile and if you have a lot of these formulas it might 
> slow down the performance
> 
Thanks, Peo.  This is more of what I as looking for.  Both formualas 
work perfectly.  If there was one thing I wish I could be smart in excel 
in, it's this type of formula building.  You are really smart.  Thanks 
again.

Ed
0
abqhusker (8)
4/10/2006 5:11:28 AM
Reply:

Similar Artilces: