How to make Horizontal data go Vertical

[tx12345]

Lets say I have this data:


(i wish there were an easy way to post a table)
a_____b____ c_____ d_____ e______ f______ g_____ h_____ i
1 - 12/1--| 385 -| 15000 -| --etc--|| --etc--|| --etc--|| --etc--||
--etc--|
2 - 12/5--| 256- | 20000 -|
3 - 12/6--| 356- | 11000 -|
4 - 12/5--| -89--| -1000 --|
5 - 12/9--| 500- | 9000 ---|

Where d1:d5 e1:e5 f1:f5 and g1:g5 h1:h5 i1:i5 all have similar data.

Is there a way to have the data from def and ghi fall in line
underneath abc with the condition being that there has to be something
in the cell?

thx

 

[Scott Wagner]

Take a look at TRANSPOSE in your Excel help, and also take a look at "Edit",
"Paste Special", "Transpose".

Let me know if you have questions.

 

[Sloth]

This might get you on the right track.

Say Sheet1 contains your data, In Sheet2 put the following formula in A1 and
copy across 3 cells and down as far as you need.
=INDIRECT("Sheet1!"&"R"&1+INT((ROW()-1)/3)&"C"&(3*MOD(ROW()-1,3)+COLUMN()),FALSE)
or
=IF(ISBLANK(INDIRECT("Sheet1!"&"R"&1+INT((ROW()-1)/3)&"C"&(3*MOD(ROW()-1,3)+COLUMN()),FALSE)),"",INDIRECT("Sheet1!"&"R"&1+INT((ROW()-1)/3)&"C"&(3*MOD(ROW()-1,3)+COLUMN()),FALSE))
the first one outputs 0 if there is nothing in the source cell, and the
second one will output "" (appears blank but will still print and is included
in the COUNT function).

 

[tx12345]

=INDIRECT("Sheet1!"&"R"&1+INT((ROW()-1)/3)&"C"&(3*MOD(ROW()-1,3)+COLUMN()),FALSE)

nice formula, but it only works with 3 sets (abc, def, ghi) I have u
to 70 sets of three to track. I have tinkered with the formula, but n
matter what i do it screws it up. If i leave it alone it only picks u
the three sets, abc, def, ghi. Any way to expand the formula t
include up to 70 sets of three, i.e., jkl, mno, pqr etc etc etc

Th

 

[Max]

Assuming source data is in Sheet1, in A1 across ..

In Sheet2,
Put in say, A1: =INDEX(Sheet1!$1:$1,,COLUMN(A1)+ROW(A1)*3-3)
Copy A1 across to C1, fill down until zeros appear
signalling exhaustion of data extracted from Sheet1's row1

(If the source data runs right across in A1:IV1 in Sheet1, we could fill
A1:C1 down till C86 [max], with A86 returning the last, rightmost value in
Sheet1's IV1. B86 and C86 would return #REF! errors)

 

[tx12345]

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

Assuming source data is in Sheet1, in A1 across ..

In Sheet2,
Put in say, A1: =INDEX(Sheet1!$1:$1,,COLUMN(A1)+ROW(A1)*3-3)
Copy A1 across to C1, fill down until zeros appear
signalling exhaustion of data extracted from Sheet1's row1

(If the source data runs right across in A1:IV1 in Sheet1, we could
fill
A1:C1 down till C86 [max], with A86 returning the last, rightmost value
in
Sheet1's IV1. B86 and C86 would return #REF! errors)

 

[Max]

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: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))

Copy across and fill down to C21000 as before

 

[Max]

Here's a sample construct:
http://cjoint.com/?mpnOPq1zhf
HorizToVerticalStacking_TX12345_wks.xls

 

[Sloth]

=INDIRECT("Sheet1!"&"R"&1+INT((ROW()-1)/70)&"C"&(3*MOD(ROW()-1,70)+COLUMN()),FALSE)

when figuring it out I used this formula
=1+INT((ROW()-1)/70)&"-"&3*MOD(ROW()-1,70)+COLUMN()
which returns something like this
1-1,1-2,1-3
1-4,1-5,1-6
....
1-208,1-209,1-210
2-1,2-2,2-3
and then put that in the indirect function in R1C1 format.

 

[tx12345]

Thanks Max

 

[Max]

You're welcome !