Let's assume source data is in Sheet1, rows 1 - 300
with data within each row in cols A to HB
(i.e. 70 days x 3 cells per day = 210 cells)

In Sheet2,

Put in A1:
=INDEX(INDIRECT("'Sheet1'!"&INT((ROWS($A$1:A1)-1)/70)+1&":"&INT((ROWS($A$1:A
1)-1)/70)+1),,COLUMN(A1)+ROW(INDIRECT("A"&MOD(ROWS($A$1: A1)-1,70)+1))*3-3)

Copy A1 across to C1, fill down to C21000 (i.e. 70 x 300)
The above will extract data from Sheet1's rows 1-300, cols A to HB,
and stack it in groups of 3 cells a-piece in the order:

Row1's data (placed within A1:C70)
Row2's data (placed within A71:C140)
Row3's data (and so on...)
....
Row300's data

And if we need the formula to return blanks: ""
instead of zeros for any blank cells in the source data,
just use an error-trap of the form: =IF(<formula=0,"",<formula)
for the formula in A1, i.e. use in A1:

=IF(INDEX(INDIRECT("'Sheet1'!"&INT((ROWS($A$1:A1)-1)/70)+1&":"&INT((ROWS($A$
1:A1)-1)/70)+1),,COLUMN(A1)+ROW(INDIRECT("A"&MOD(ROWS($A$1: A1)-1,70)+1))*3-3
)=0,"",INDEX(INDIRECT("'Sheet1'!"&INT((ROWS($A$1:A 1)-1)/70)+1&":"&INT((ROWS(
$A$1:A1)-1)/70)+1),,COLUMN(A1)+ROW(INDIRECT("A"&MOD(ROWS($A$1: A1)-1,70)+1))*
3-3))

Copy across and fill down to C21000 as before
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"tx12345" wrote in
message ...

Hi Max,

Your code works great, but for one row. I also have to get all the
other rows procesed and have them end up stacked on top of eachother
vertically

Lets say I have 24 rows running down, each with 3 cells running across
per day. The number of days will vary, and it can go on for up to 70
days, but the average is around 10 - 20

1 row of data, 3 cells per day, 20 days equals 20 rows, times howeer
many rows i have got.

row 1 1 2 4| 4 5 6 | 4 5 6 | 6 7 8 etc up to 70 days
row 2 8 8 9| 6 7 8 | 0 0 9 etc up to 70 days
row 3 3 5 6| etc up to 70 days
v
v
on down to as many as 300 rows




So I am looking for that magic formula that will make the data look
like this:

1 2 4
4 5 6
6 7 8
8 8 9
6 7 8
0 0 9
3 5 6

When only enough space is needed for the data that exists. Yes,
convceivably I would need vertical space for 300 rows time 70 days, or
21000 rows in a nice vertical column, but it is not likely I'll need
anything close to that. Also, the data once gone vertical needs to be
sorted, subtotalled, and charted as well, so the less space used the
better.

I could just do the =a1 thing, forcing all 21000 rows to statically
exist waiting for the data to arrive, but there has to be a better
way.

Thanks for your input. Always appreciated.

Txx