collecting cell data from every 33rd row
 

Could somebody please help me, I have a big file (over 2000 rows) in excel
and I would need to collect every 33rd cells in the same column one after
another to other column. It makes easier to handle data and draw charts...
Can i write a formula that would add 33 rows to previous cells formula ( like
=D10 +10 rows downwards so the next cell would be =D43)?

Assuming the column whose data you want to summarize is A, and that your
summary starts in row 2, put this formula in row 2 and copy it down:

=OFFSET($A$10,(ROW()-2)*33,0)

If the summary is going to start in some row other than 2, change the
ROW()-2 so that it is ROW()-starting row #.





"Rebecca" wrote:

Could somebody please help me, I have a big file (over 2000 rows) in excel
and I would need to collect every 33rd cells in the same column one after
another to other column. It makes easier to handle data and draw charts...
Can i write a formula that would add 33 rows to previous cells formula ( like
=D10 +10 rows downwards so the next cell would be =D43)?

One way ..

Assuming data in col D, D10 down

Put in any starting cell, say, in E10:

=OFFSET($D$10,ROWS($A$1:A1)*33-33,)

Copy E10 down E71 thereabouts to complete the extract for 2000 rows
(until zeros appear, signalling exhaustion of data extract)
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Rebecca" wrote in message
...
Could somebody please help me, I have a big file (over 2000 rows) in excel
and I would need to collect every 33rd cells in the same column one after
another to other column. It makes easier to handle data and draw charts...
Can i write a formula that would add 33 rows to previous cells formula (
like
=D10 +10 rows downwards so the next cell would be =D43)?

try this one:

=OFFSET($D$2,(ROW()-ROW($D$2))*33,)

Regards,
KL


"Rebecca" wrote in message
...
Could somebody please help me, I have a big file (over 2000 rows) in excel
and I would need to collect every 33rd cells in the same column one after
another to other column. It makes easier to handle data and draw charts...
Can i write a formula that would add 33 rows to previous cells formula (
like
=D10 +10 rows downwards so the next cell would be =D43)?

I am not quite understand what you want to do, but if you want to pull
out every 33rd data and store them in a different column you can try:

Assume A3:A2000 is your data

B3 = INDIRECT("A"&(ROW()-3)*33+3) (Copy down)


Hope this helps.



Rebecca Wrote:
Could somebody please help me, I have a big file (over 2000 rows) in
excel
and I would need to collect every 33rd cells in the same column one
after
another to other column. It makes easier to handle data and draw
charts...
Can i write a formula that would add 33 rows to previous cells formula
( like
=D10 +10 rows downwards so the next cell would be =D43)?


--
Morrigan
------------------------------------------------------------------------
Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094
View this thread: http://www.excelforum.com/showthread...hreadid=389348