Multiple columns into one column?

[Ipinho100]

Hi

I have 15 columns and each have over 300 rows of information. I would like to get all the data from the 15 columns into one column. The new column would have the 15*>300 rows. What would be the easiest way to do it?

[avk]

For example your data "A2" to "D3" Here i m calculating only 4 columns & two row. you can change as per your requirement.
Formula is :

[Pete_UK]

How do you want the data to appear? Take all 300+ values from the first column, followed by the next column, and so on, or take the 15 values from the first row, followed by the next row, and so on?

Pete

[Ipinho100]

Take all 300+ values from the first column, followed by the next column

[Pete_UK]

Okay, assuming your data is in cells A1 down across to column O, you could put this formula (e.g. in cell Q1):

=IF(ROWS($1:1)>COUNTA($A:$A)*COUNTA($A$1:$O$1),"",INDEX($A:$O,MOD(ROWS($1:1)-1,COUNTA($A:$A))+1,INT((ROWS($1:1)-1)/COUNTA($A:$A))+1))

then copy down as far as you need to.

NOTE: as you are in Finland you may need to use a semicolon ( ; ) instead of a comma ( , ) in the formula, and you may need to translate the function names, depending on your regional settings.

Hope this helps.

Pete

[avk]

Take all 300+ values from the first column, followed by the next column

Try with "Concatenate" formula.

[Pete_UK]

Try with "Concatenate" formula.

I don't think the OP wants all the data in one cell.

@Ipinho,

I've attached my test file, with a small amount of data, so you can see how the formula will get translated into your version. It will automatically expand when you have more data, so just copy the formula down until you start to get blanks.

Hope this helps.

Pete

[avk]

I don't think the OP wants all the data in one cell.

@Ipinho,

I've attached my test file, with a small amount of data, so you can see how the formula will get translated into your version. It will automatically expand when you have more data, so just copy the formula down until you start to get blanks.

Hope this helps.

Pete

Dear Pete sir, Yes you are write.

[Ipinho100]

Thanks a lot. It works now.