CREATE A COLUMN from more column ...

**libero8484** · 03-26-2007, 08:56 AM

does someone know if it is possible to create a column with data from many columns?

I have 52 columns with in each 1500 Numbers

I want to create a column that contains all the (1500*52) = 78000 numbers.

Is it possible?

Thank you!

**oldchippy** · 03-26-2007, 09:04 AM

Is this what you are trying to achieve, have a look at Starguy's spreadsheet here

http://www.excelforum.com/showthread...80#post1766880

**starguy** · 03-26-2007, 09:05 AM

Originally Posted by libero8484

does someone know if it is possible to create a column with data from many columns?

I have 52 columns with in each 1500 Numbers

I want to create a column that contains all the (1500*52) = 78000 numbers.

Is it possible?

Thank you!

Excel 2003 will not allow to enter more than 65536 values in a single column however it is possible in Excel 2007.

**mudraker** · 03-26-2007, 09:05 AM

I assume when you say 1500 hundred numbers you are referring to 1500 rows.

As to is it possible all depends on how big the numbers are & how you want them combined into 1 column

**starguy** · 03-26-2007, 09:13 AM

Originally Posted by oldchippy

Is this what you are trying to achieve, have a look at Starguy's spreadsheet here

http://www.excelforum.com/showthread...80#post1766880

Hi oldchippy

that spreadsheet may not be useful for him because he wants exactly reverse of what is in that spreadsheet.

**oldchippy** · 03-26-2007, 09:15 AM

Ok, well perhaps reverse engineering may come into play

**libero8484** · 03-26-2007, 09:17 AM

I have x Columns and want have only 1 column!!!!!

with office 2007

**libero8484** · 03-26-2007, 09:31 AM

does somonw have a sulutions?

**starguy** · 03-26-2007, 09:50 AM

Originally Posted by libero8484

does somonw have a sulutions?

here is the easy solution (if you have same number of rows with data in each column)

I suppose you have data in col B, C, D and onwards... and values in each column end in row # 1500 i-e values in col B end in cell B1500, in col C end in cell C1500 and so on in each column.
in cell B1501 put
=C1
and copy it to right side for 52 columns and downwards for 78000 rows.

see col B will show you all values.
is'nt it???

**oldchippy** · 03-26-2007, 09:51 AM

There you go - reverse engineering!

**Bryan Hessey** · 03-26-2007, 10:19 AM

Originally Posted by oldchippy

There you go - reverse engineering!

Hi,

another way, Insert a new column A and in A1 put

=OFFSET($B$1,MOD(ROW()-1,1500),INT((ROW()-1)/1500))

and fill that down to row 65536

hth
---
added, with 2007, fill down as far as required, the easy way, Copy A1
type A1:A80000 in the Name box, then Paste.
---

**libero8484** · 03-26-2007, 12:11 PM

how dows it work?

it give me an error as result

**Bryan Hessey** · 03-26-2007, 05:43 PM

Originally Posted by libero8484

how dows it work?

it give me an error as result

?

it works by putting that formula into any cell that the formula does not include, ie, put it into a new column A, or column Z

What 'error' did you get, and at what point?

can you paste the formula that you entered back here.

---

**starguy** · 03-27-2007, 01:27 AM

Originally Posted by Bryan Hessey

?

it works by putting that formula into any cell that the formula does not include, ie, put it into a new column A, or column Z

What 'error' did you get, and at what point?

can you paste the formula that you entered back here.

---

Hi Bryan

see the attached file for both my tip and your formula.

**Bryan Hessey** · 03-27-2007, 03:32 AM

Originally Posted by starguy

Hi Bryan

see the attached file for both my tip and your formula.

Hi Starguy,
the formula was in reply to the original
"I have 52 columns with in each 1500 Numbers" and as such would require a re-work for just 5 lines.

The advantage was that the formula could be entered in A1, Copied, set the range A1:A78000 (and press Enter) in the Name box and then Paste to produce a full list.

However, whatever works easiest for the OP, in either case the result will need to be followed by a Copy then Paste Special = Values before deleting the chaffe.

---

**starguy** · 03-28-2007, 08:51 AM

Originally Posted by Bryan Hessey

Hi Starguy,
the formula was in reply to the original
"I have 52 columns with in each 1500 Numbers" and as such would require a re-work for just 5 lines.

The advantage was that the formula could be entered in A1, Copied, set the range A1:A78000 (and press Enter) in the Name box and then Paste to produce a full list.

However, whatever works easiest for the OP, in either case the result will need to be followed by a Copy then Paste Special = Values before deleting the chaffe.

---

ok got it.

**djuMSze** · 01-13-2011, 08:58 PM

Hi Bryan,

I took a look at your .zip file. And I am not sure if:
Single Col.zip (2.3 KB, 18 views)
answers my question.

I have 400 columns and each column has unique values (cases). I want these cases to be in one variable (column) following the sequence of each case column 1 to column 400 downwards.

Supposed, I have:

A B C
1 4 7
2 5 8
3 6 9

I want these cases to look as:

A
1
2
3
4
5
6
7

It seems to me that the .zip you posted didn't contain the original values. Correct me If I am mistaken. Thank you.

**djuMSze** · 01-13-2011, 10:39 PM

This is my sample worksheet. Thanks.

I want to transpose everything from A1:CW101 in one single column with the sequence A1:A101, B1:B101 and so on..

**teylyn** · 01-13-2011, 10:43 PM

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.

Also: This thread is from 2007, almost 4 years old, and ...

This is a duplicate post and as such does not comply with Rule 5 of our forum rules. This thread will now be closed, you may continue in your other thread.

Thread Closed.