software

Hi everybody, yet another Excel question from me.

I was looking through the knowledge base to try and find how to combine data
from multiple columns into one on a new sheet, so

a b c
a b
a b
a

would become like this.

a
a
a
a
b
b
b
c

I found this formula, and it definitely works for 30 rows in each column, as
the person who asked the original question wanted, but is there any way to
modify this formula so columns with varying lengths will still work without
zeros in between them? I've never seen these functions before, so I don't
know how to modify it to make it work. My column lengths are currently
between 2700 and 3400 rows, but they'll probably get longer, and I don't want
to have to cut and paste all of them. The columns go from A to AZ, and if I
keep all the zeros in there it will overflow the maximum row limit for
worksheets, plus it'll be harder to work with. My columns also have gaps in
them, so some of them don't start on the same rows and some rows don't have
values for every column. Is there a way to do this?

=OFFSET(INDIRECT(quot;Sheet1!quot;amp;CHAR(INT((ROWS(Sheet1!$ A$1:A1)-1)/30) 65)amp;quot;1quot;),MOD(ROWS(Sheet1!$A$1:A1)-1,30),)

Just to be clear, my colums look more like:

a
a b
a b
b
b c
a b c
a c
a
a c
c

Thanks so much!
You will need some VBA macro code. Do you mind?

Cheers,
--
AP

quot;jezzica85quot; gt; a écrit dans le message de
...
gt; Hi everybody, yet another Excel question from me.
gt;
gt; I was looking through the knowledge base to try and find how to combine
data
gt; from multiple columns into one on a new sheet, so
gt;
gt; a b c
gt; a b
gt; a b
gt; a
gt;
gt; would become like this.
gt;
gt; a
gt; a
gt; a
gt; a
gt; b
gt; b
gt; b
gt; c
gt;
gt; I found this formula, and it definitely works for 30 rows in each column,
as
gt; the person who asked the original question wanted, but is there any way to
gt; modify this formula so columns with varying lengths will still work
without
gt; zeros in between them? I've never seen these functions before, so I don't
gt; know how to modify it to make it work. My column lengths are currently
gt; between 2700 and 3400 rows, but they'll probably get longer, and I don't
want
gt; to have to cut and paste all of them. The columns go from A to AZ, and if
I
gt; keep all the zeros in there it will overflow the maximum row limit for
gt; worksheets, plus it'll be harder to work with. My columns also have gaps
in
gt; them, so some of them don't start on the same rows and some rows don't
have
gt; values for every column. Is there a way to do this?
gt;
gt;
=OFFSET(INDIRECT(quot;Sheet1!quot;amp;CHAR(INT((ROWS(Sheet1!$ A$1:A1)-1)/30) 65)amp;quot;1quot;),MO
D(ROWS(Sheet1!$A$1:A1)-1,30),)
gt;
gt; Just to be clear, my colums look more like:
gt;
gt; a
gt; a b
gt; a b
gt; b
gt; b c
gt; a b c
gt; a c
gt; a
gt; a c
gt; c
gt;
gt; Thanks so much!
gt;
gt;
You have a response at your other post.

jezzica85 wrote:
gt;
gt; Hi everybody, yet another Excel question from me.
gt;
gt; I was looking through the knowledge base to try and find how to combine data
gt; from multiple columns into one on a new sheet, so
gt;
gt; a b c
gt; a b
gt; a b
gt; a
gt;
gt; would become like this.
gt;
gt; a
gt; a
gt; a
gt; a
gt; b
gt; b
gt; b
gt; c
gt;
gt; I found this formula, and it definitely works for 30 rows in each column, as
gt; the person who asked the original question wanted, but is there any way to
gt; modify this formula so columns with varying lengths will still work without
gt; zeros in between them? I've never seen these functions before, so I don't
gt; know how to modify it to make it work. My column lengths are currently
gt; between 2700 and 3400 rows, but they'll probably get longer, and I don't want
gt; to have to cut and paste all of them. The columns go from A to AZ, and if I
gt; keep all the zeros in there it will overflow the maximum row limit for
gt; worksheets, plus it'll be harder to work with. My columns also have gaps in
gt; them, so some of them don't start on the same rows and some rows don't have
gt; values for every column. Is there a way to do this?
gt;
gt; =OFFSET(INDIRECT(quot;Sheet1!quot;amp;CHAR(INT((ROWS(Sheet1!$ A$1:A1)-1)/30) 65)amp;quot;1quot;),MOD(ROWS(Sheet1!$A$1:A1)-1,30),)
gt;
gt; Just to be clear, my colums look more like:
gt;
gt; a
gt; a b
gt; a b
gt; b
gt; b c
gt; a b c
gt; a c
gt; a
gt; a c
gt; c
gt;
gt; Thanks so much!

--

Dave Peterson