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.

"tx12345" wrote:

>
> 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
>
>
> --
> tx12345
> ------------------------------------------------------------------------
> tx12345's Profile: http://www.excelforum.com/member.php...o&userid=24776
> View this thread: http://www.excelforum.com/showthread...hreadid=493109
>
>

[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" wrote:

>
> 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
>
>
> --
> tx12345
> ------------------------------------------------------------------------
> tx12345's Profile: http://www.excelforum.com/member.php...o&userid=24776
> View this thread: http://www.excelforum.com/showthread...hreadid=493109
>
>

[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 up to 70 sets of three to track. I have tinkered with the formula, but no matter what i do it screws it up. If i leave it alone it only picks up the three sets, abc, def, ghi. Any way to expand the formula to include up to 70 sets of three, i.e., jkl, mno, pqr etc etc etc

Thx

[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)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"tx12345" <[email protected]> wrote in
message news:[email protected]...
>
> >
=INDIRECT("Sheet1!"&"R"&1+INT((ROW()-1)/3)&"C"&(3*MOD(ROW()-1,3)+COLUMN()),F
ALSE)
>
> nice formula, but it only works with 3 sets (abc, def, ghi) I have up
> to 70 sets of three to track. I have tinkered with the formula, but no
> matter what i do it screws it up. If i leave it alone it only picks up
> the three sets, abc, def, ghi. Any way to expand the formula to
> include up to 70 sets of three, i.e., jkl, mno, pqr etc etc etc
>
> Thx
>
>
> --
> tx12345
> ------------------------------------------------------------------------
> tx12345's Profile:
http://www.excelforum.com/member.php...o&userid=24776
> View this thread: http://www.excelforum.com/showthread...hreadid=493109
>

[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)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895

[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
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"tx12345" <[email protected]> wrote in
message news:[email protected]...
>
> 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

[Max]

Here's a sample construct:
http://cjoint.com/?mpnOPq1zhf
HorizToVerticalStacking_TX12345_wks.xls
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

[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" wrote:

>
> > =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 up
> to 70 sets of three to track. I have tinkered with the formula, but no
> matter what i do it screws it up. If i leave it alone it only picks up
> the three sets, abc, def, ghi. Any way to expand the formula to
> include up to 70 sets of three, i.e., jkl, mno, pqr etc etc etc
>
> Thx
>
>
> --
> tx12345
> ------------------------------------------------------------------------
> tx12345's Profile: http://www.excelforum.com/member.php...o&userid=24776
> View this thread: http://www.excelforum.com/showthread...hreadid=493109
>
>

[tx12345]

Thanks Max

[Max]

You're welcome !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"tx12345" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks Max