Combining columns corrected
 

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("Sheet1!"&CHAR(INT((ROWS(Sheet1!$ A$1:A1)-1)/30)+65)&"1"),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!

Combining columns corrected
 

You will need some VBA macro code. Do you mind?

Cheers,
--
AP

"jezzica85" a écrit dans le message de
...
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("Sheet1!"&CHAR(INT((ROWS(Sheet1!$ A$1:A1)-1)/30)+65)&"1"),MO
D(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!

Combining columns corrected
 

You have a response at your other post.

jezzica85 wrote:

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("Sheet1!"&CHAR(INT((ROWS(Sheet1!$ A$1:A1)-1)/30)+65)&"1"),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!

--

Dave Peterson