moving data from numerous colums into one colum
 

Hi,

I have to move the data from numerous columns into one colum as a list,
how can i do this?

eg
1 2 3 4 5 6 7 8 9 10
1 2 3 4 5 6 7 8 9 10

to
1
2
3
4
5
6
7
8
9
10
1
2
3
4
5
6
7
8
9
10

please help?

thanks,

moving data from numerous colums into one colum
 

ACTUALLY THE DATA SHOULD READ,

1
1
2
2
3
3
4
4
5
5
6
6
7
7
8
8
9
9
10
10

NOT AS PREVIOUS STATED

THANKS,

TERESA
"teresa" wrote:

Hi,

I have to move the data from numerous columns into one colum as a list,
how can i do this?

eg
1 2 3 4 5 6 7 8 9 10
1 2 3 4 5 6 7 8 9 10

to
1
2
3
4
5
6
7
8
9
10
1
2
3
4
5
6
7
8
9
10

please help?

thanks,

moving data from numerous colums into one colum
 

Hi teresa
data: A1:J10

Try this formula in cell A11
=OFFSET($A$1,INT((ROW()-11)/10),MOD(ROW()-1,10))
Copy down as far as needed

Regards,
Pedro J.

Hi,

I have to move the data from numerous columns into one colum as a list,
how can i do this?

eg
1 2 3 4 5 6 7 8 9 10
1 2 3 4 5 6 7 8 9 10

to
1
2
3
4
5
6
7
8
9
10
1
2
3
4
5
6
7
8
9
10

please help?

thanks,

moving data from numerous colums into one colum
 

HI THIS WORKS, BUT IF U CHECK MY SECOND NOTE, THE INFORMATION SHOULD READ THE
OTHER WAY,

THANKS,

TERESA


"Infinitogool" wrote:

Hi teresa
data: A1:J10

Try this formula in cell A11
=OFFSET($A$1,INT((ROW()-11)/10),MOD(ROW()-1,10))
Copy down as far as needed

Regards,
Pedro J.

Hi,

I have to move the data from numerous columns into one colum as a list,
how can i do this?

eg
1 2 3 4 5 6 7 8 9 10
1 2 3 4 5 6 7 8 9 10

to
1
2
3
4
5
6
7
8
9
10
1
2
3
4
5
6
7
8
9
10

please help?

thanks,

moving data from numerous colums into one colum
 

If you copy the cells that this technique gives you, "Paste Special" them on
top as values, you can then sort them as you see fit. Just make sure you
don't need to reference the original cells.

moving data from numerous colums into one colum
 

Another way, using above example that your data is in A1:J10, assuming all
data are numbers! (and all cells are occupied).
in A11, type the formula: =SMALL($A$1:$J$10,ROW()-10), and copy down as
needed.

--
John C


"teresa" wrote:

HI THIS WORKS, BUT IF U CHECK MY SECOND NOTE, THE INFORMATION SHOULD READ THE
OTHER WAY,

THANKS,

TERESA


"Infinitogool" wrote:

Hi teresa
data: A1:J10

Try this formula in cell A11
=OFFSET($A$1,INT((ROW()-11)/10),MOD(ROW()-1,10))
Copy down as far as needed

Regards,
Pedro J.

Hi,

I have to move the data from numerous columns into one colum as a list,
how can i do this?

eg
1 2 3 4 5 6 7 8 9 10
1 2 3 4 5 6 7 8 9 10

to
1
2
3
4
5
6
7
8
9
10
1
2
3
4
5
6
7
8
9
10

please help?

thanks,

moving data from numerous colums into one colum
 

Sub transposerowstocola()
lr = Cells(rows.Count, 1).End(xlUp).Row
For i = 1 To lr
lc = Cells(i, Columns.Count).End(xlToLeft).Column
dlr = Cells(rows.Count, 1).End(xlUp).Row + 1
Range(Cells(i, 1), Cells(i, lc)).Copy
Cells(dlr, 1).PasteSpecial Paste:=xlPasteAll, Transpose:=True
Next i
rows("1:" & lr).Delete
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"teresa" wrote in message
...
Hi,

I have to move the data from numerous columns into one colum as a list,
how can i do this?

eg
1 2 3 4 5 6 7 8 9 10
1 2 3 4 5 6 7 8 9 10

to
1
2
3
4
5
6
7
8
9
10
1
2
3
4
5
6
7
8
9
10

please help?

thanks,

moving data from numerous colums into one colum
 

NO THERE IS BOTH NUMBERS AND LETTERS?

ANY OTHER SUGGESTIONS,
THANKS,

"John C" wrote:

Another way, using above example that your data is in A1:J10, assuming all
data are numbers! (and all cells are occupied).
in A11, type the formula: =SMALL($A$1:$J$10,ROW()-10), and copy down as
needed.

--
John C


"teresa" wrote:

HI THIS WORKS, BUT IF U CHECK MY SECOND NOTE, THE INFORMATION SHOULD READ THE
OTHER WAY,

THANKS,

TERESA


"Infinitogool" wrote:

Hi teresa
data: A1:J10

Try this formula in cell A11
=OFFSET($A$1,INT((ROW()-11)/10),MOD(ROW()-1,10))
Copy down as far as needed

Regards,
Pedro J.

Hi,

I have to move the data from numerous columns into one colum as a list,
how can i do this?

eg
1 2 3 4 5 6 7 8 9 10
1 2 3 4 5 6 7 8 9 10

to
1
2
3
4
5
6
7
8
9
10
1
2
3
4
5
6
7
8
9
10

please help?

thanks,

moving data from numerous colums into one colum
 

Based on your CHANGE. Why can't women make up their minds <G
From a recent and similar posting

Sub copyallcolstocolA()
For i = 2 To Cells(1, Columns.Count).End(xlToLeft).Column
cl = Cells(rows.Count, i).End(xlUp).Row
dlr = Cells(rows.Count, 1).End(xlUp).Row + 1
Range(Cells(1, i), Cells(cl, i)).Copy Cells(dlr, 1)
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"teresa" wrote in message
...

ACTUALLY THE DATA SHOULD READ,

1
1
2
2
3
3
4
4
5
5
6
6
7
7
8
8
9
9
10
10

NOT AS PREVIOUS STATED

THANKS,

TERESA
"teresa" wrote:

Hi,

I have to move the data from numerous columns into one colum as a list,
how can i do this?

eg
1 2 3 4 5 6 7 8 9 10
1 2 3 4 5 6 7 8 9 10

to
1
2
3
4
5
6
7
8
9
10
1
2
3
4
5
6
7
8
9
10

please help?

thanks,

moving data from numerous colums into one colum
 

HOW CAN YOU DO THIS AS A FORMULA??
SORRY THIS HAS CONFUSED ME MORE?

THANKS,

TERESA


"Don Guillett" wrote:

Sub transposerowstocola()
lr = Cells(rows.Count, 1).End(xlUp).Row
For i = 1 To lr
lc = Cells(i, Columns.Count).End(xlToLeft).Column
dlr = Cells(rows.Count, 1).End(xlUp).Row + 1
Range(Cells(i, 1), Cells(i, lc)).Copy
Cells(dlr, 1).PasteSpecial Paste:=xlPasteAll, Transpose:=True
Next i
rows("1:" & lr).Delete
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"teresa" wrote in message
...
Hi,

I have to move the data from numerous columns into one colum as a list,
how can i do this?

eg
1 2 3 4 5 6 7 8 9 10
1 2 3 4 5 6 7 8 9 10

to
1
2
3
4
5
6
7
8
9
10
1
2
3
4
5
6
7
8
9
10

please help?

thanks,

moving data from numerous colums into one colum
 

Then you would want to use Infinitogool's suggestion for offset. Copy and
paste special as suggested per Gaijintendo, then sort.
--
John C


"teresa" wrote:

NO THERE IS BOTH NUMBERS AND LETTERS?

ANY OTHER SUGGESTIONS,
THANKS,

"John C" wrote:

Another way, using above example that your data is in A1:J10, assuming all
data are numbers! (and all cells are occupied).
in A11, type the formula: =SMALL($A$1:$J$10,ROW()-10), and copy down as
needed.

--
John C


"teresa" wrote:

HI THIS WORKS, BUT IF U CHECK MY SECOND NOTE, THE INFORMATION SHOULD READ THE
OTHER WAY,

THANKS,

TERESA


"Infinitogool" wrote:

Hi teresa
data: A1:J10

Try this formula in cell A11
=OFFSET($A$1,INT((ROW()-11)/10),MOD(ROW()-1,10))
Copy down as far as needed

Regards,
Pedro J.

Hi,

I have to move the data from numerous columns into one colum as a list,
how can i do this?

eg
1 2 3 4 5 6 7 8 9 10
1 2 3 4 5 6 7 8 9 10

to
1
2
3
4
5
6
7
8
9
10
1
2
3
4
5
6
7
8
9
10

please help?

thanks,

moving data from numerous colums into one colum
 

Hi teresa
data: A1:J10

Try this formula in cell A11
=OFFSET($A$1,MOD(ROW()-1,10),INT((ROW()-11)/10))
Copy down as far as needed

Regards,
Pedro J.
ACTUALLY THE DATA SHOULD READ,

1
1
2
2
3
3
4
4
5
5
6
6
7
7
8
8
9
9
10
10

NOT AS PREVIOUS STATED

THANKS,

TERESA
"teresa" wrote:

Hi,

I have to move the data from numerous columns into one colum as a list,
how can i do this?

eg
1 2 3 4 5 6 7 8 9 10
1 2 3 4 5 6 7 8 9 10

to
1
2
3
4
5
6
7
8
9
10
1
2
3
4
5
6
7
8
9
10

please help?

thanks,

moving data from numerous colums into one colum
 

You are absolutely correct. But then, so am I.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"John C" <johnc@stateofdenial wrote in message
...
Formula solutions have been given. Some people aren't comfortable with
macros, or VBA for that matter. To say that your way is the 'proper' way,
and
that other ways are subsequently improper is both arrogant and pompous.
The
macro way is typically cleaner, but if it is a one time rearrangement, for
someone to go through all the learning of macros, and operation thereof I
think is a bit much.
--
John C


"Don Guillett" wrote:


Please don't shout. I can hear well despite my age. I would NOT use a
formula for this. You should learn how to do it properly.

If you're new to macros, you may want to read David McRitchie's
intro
at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"teresa" wrote in message
...
HOW CAN YOU DO THIS AS A FORMULA??
SORRY THIS HAS CONFUSED ME MORE?

THANKS,

TERESA


"Don Guillett" wrote:

Sub transposerowstocola()
lr = Cells(rows.Count, 1).End(xlUp).Row
For i = 1 To lr
lc = Cells(i, Columns.Count).End(xlToLeft).Column
dlr = Cells(rows.Count, 1).End(xlUp).Row + 1
Range(Cells(i, 1), Cells(i, lc)).Copy
Cells(dlr, 1).PasteSpecial Paste:=xlPasteAll, Transpose:=True
Next i
rows("1:" & lr).Delete
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"teresa" wrote in message
...
Hi,

I have to move the data from numerous columns into one colum as a
list,
how can i do this?

eg
1 2 3 4 5 6 7 8 9 10
1 2 3 4 5 6 7 8 9 10

to
1
2
3
4
5
6
7
8
9
10
1
2
3
4
5
6
7
8
9
10

please help?

thanks,

moving data from numerous colums into one colum
 

No. If you have been using excel for long, which I am assuming you have, then
you should be the first to know that there are MANY ways to achieve the same
result. Just because you use a macro, does not make it the 'proper' way.
--
John C


"Don Guillett" wrote:

You are absolutely correct. But then, so am I.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"John C" <johnc@stateofdenial wrote in message
...
Formula solutions have been given. Some people aren't comfortable with
macros, or VBA for that matter. To say that your way is the 'proper' way,
and
that other ways are subsequently improper is both arrogant and pompous.
The
macro way is typically cleaner, but if it is a one time rearrangement, for
someone to go through all the learning of macros, and operation thereof I
think is a bit much.
--
John C


"Don Guillett" wrote:


Please don't shout. I can hear well despite my age. I would NOT use a
formula for this. You should learn how to do it properly.

If you're new to macros, you may want to read David McRitchie's
intro
at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"teresa" wrote in message
...
HOW CAN YOU DO THIS AS A FORMULA??
SORRY THIS HAS CONFUSED ME MORE?

THANKS,

TERESA


"Don Guillett" wrote:

Sub transposerowstocola()
lr = Cells(rows.Count, 1).End(xlUp).Row
For i = 1 To lr
lc = Cells(i, Columns.Count).End(xlToLeft).Column
dlr = Cells(rows.Count, 1).End(xlUp).Row + 1
Range(Cells(i, 1), Cells(i, lc)).Copy
Cells(dlr, 1).PasteSpecial Paste:=xlPasteAll, Transpose:=True
Next i
rows("1:" & lr).Delete
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"teresa" wrote in message
...
Hi,

I have to move the data from numerous columns into one colum as a
list,
how can i do this?

eg
1 2 3 4 5 6 7 8 9 10
1 2 3 4 5 6 7 8 9 10

to
1
2
3
4
5
6
7
8
9
10
1
2
3
4
5
6
7
8
9
10

please help?

thanks,

moving data from numerous colums into one colum
 

Guys no need to argue,

i got it sorted,

Ive another problem if anyone would like to help with that?

thanks,

teresa


"John C" wrote:

No. If you have been using excel for long, which I am assuming you have, then
you should be the first to know that there are MANY ways to achieve the same
result. Just because you use a macro, does not make it the 'proper' way.
--
John C


"Don Guillett" wrote:

You are absolutely correct. But then, so am I.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"John C" <johnc@stateofdenial wrote in message
...
Formula solutions have been given. Some people aren't comfortable with
macros, or VBA for that matter. To say that your way is the 'proper' way,
and
that other ways are subsequently improper is both arrogant and pompous.
The
macro way is typically cleaner, but if it is a one time rearrangement, for
someone to go through all the learning of macros, and operation thereof I
think is a bit much.
--
John C


"Don Guillett" wrote:


Please don't shout. I can hear well despite my age. I would NOT use a
formula for this. You should learn how to do it properly.

If you're new to macros, you may want to read David McRitchie's
intro
at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"teresa" wrote in message
...
HOW CAN YOU DO THIS AS A FORMULA??
SORRY THIS HAS CONFUSED ME MORE?

THANKS,

TERESA


"Don Guillett" wrote:

Sub transposerowstocola()
lr = Cells(rows.Count, 1).End(xlUp).Row
For i = 1 To lr
lc = Cells(i, Columns.Count).End(xlToLeft).Column
dlr = Cells(rows.Count, 1).End(xlUp).Row + 1
Range(Cells(i, 1), Cells(i, lc)).Copy
Cells(dlr, 1).PasteSpecial Paste:=xlPasteAll, Transpose:=True
Next i
rows("1:" & lr).Delete
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"teresa" wrote in message
...
Hi,

I have to move the data from numerous columns into one colum as a
list,
how can i do this?

eg
1 2 3 4 5 6 7 8 9 10
1 2 3 4 5 6 7 8 9 10

to
1
2
3
4
5
6
7
8
9
10
1
2
3
4
5
6
7
8
9
10

please help?

thanks,

moving data from numerous colums into one colum
 

Because I am pompous and arrogant, I say that it IS the proper way IN THIS
CASE. I think it absolutely silly to burden a project with unnecessary
calculations and other formula overhead. Of course, the formula solution
could be used but the results should then be converted to values.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"John C" <johnc@stateofdenial wrote in message
...
No. If you have been using excel for long, which I am assuming you have,
then
you should be the first to know that there are MANY ways to achieve the
same
result. Just because you use a macro, does not make it the 'proper' way.
--
John C


"Don Guillett" wrote:

You are absolutely correct. But then, so am I.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"John C" <johnc@stateofdenial wrote in message
...
Formula solutions have been given. Some people aren't comfortable with
macros, or VBA for that matter. To say that your way is the 'proper'
way,
and
that other ways are subsequently improper is both arrogant and pompous.
The
macro way is typically cleaner, but if it is a one time rearrangement,
for
someone to go through all the learning of macros, and operation thereof
I
think is a bit much.
--
John C


"Don Guillett" wrote:


Please don't shout. I can hear well despite my age. I would NOT use a
formula for this. You should learn how to do it properly.

If you're new to macros, you may want to read David McRitchie's
intro
at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"teresa" wrote in message
...
HOW CAN YOU DO THIS AS A FORMULA??
SORRY THIS HAS CONFUSED ME MORE?

THANKS,

TERESA


"Don Guillett" wrote:

Sub transposerowstocola()
lr = Cells(rows.Count, 1).End(xlUp).Row
For i = 1 To lr
lc = Cells(i, Columns.Count).End(xlToLeft).Column
dlr = Cells(rows.Count, 1).End(xlUp).Row + 1
Range(Cells(i, 1), Cells(i, lc)).Copy
Cells(dlr, 1).PasteSpecial Paste:=xlPasteAll, Transpose:=True
Next i
rows("1:" & lr).Delete
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"teresa" wrote in message
...
Hi,

I have to move the data from numerous columns into one colum as a
list,
how can i do this?

eg
1 2 3 4 5 6 7 8 9 10
1 2 3 4 5 6 7 8 9 10

to
1
2
3
4
5
6
7
8
9
10
1
2
3
4
5
6
7
8
9
10

please help?

thanks,

moving data from numerous colums into one colum
 

As has been stated by several posters. Of course, you obviously didn't read
them, or you would have noted that already.
--
John C


"Don Guillett" wrote:


Because I am pompous and arrogant, I say that it IS the proper way IN THIS
CASE. I think it absolutely silly to burden a project with unnecessary
calculations and other formula overhead. Of course, the formula solution
could be used but the results should then be converted to values.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"John C" <johnc@stateofdenial wrote in message
...
No. If you have been using excel for long, which I am assuming you have,
then
you should be the first to know that there are MANY ways to achieve the
same
result. Just because you use a macro, does not make it the 'proper' way.
--
John C


"Don Guillett" wrote:

You are absolutely correct. But then, so am I.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"John C" <johnc@stateofdenial wrote in message
...
Formula solutions have been given. Some people aren't comfortable with
macros, or VBA for that matter. To say that your way is the 'proper'
way,
and
that other ways are subsequently improper is both arrogant and pompous.
The
macro way is typically cleaner, but if it is a one time rearrangement,
for
someone to go through all the learning of macros, and operation thereof
I
think is a bit much.
--
John C


"Don Guillett" wrote:


Please don't shout. I can hear well despite my age. I would NOT use a
formula for this. You should learn how to do it properly.

If you're new to macros, you may want to read David McRitchie's
intro
at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"teresa" wrote in message
...
HOW CAN YOU DO THIS AS A FORMULA??
SORRY THIS HAS CONFUSED ME MORE?

THANKS,

TERESA


"Don Guillett" wrote:

Sub transposerowstocola()
lr = Cells(rows.Count, 1).End(xlUp).Row
For i = 1 To lr
lc = Cells(i, Columns.Count).End(xlToLeft).Column
dlr = Cells(rows.Count, 1).End(xlUp).Row + 1
Range(Cells(i, 1), Cells(i, lc)).Copy
Cells(dlr, 1).PasteSpecial Paste:=xlPasteAll, Transpose:=True
Next i
rows("1:" & lr).Delete
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"teresa" wrote in message
...
Hi,

I have to move the data from numerous columns into one colum as a
list,
how can i do this?

eg
1 2 3 4 5 6 7 8 9 10
1 2 3 4 5 6 7 8 9 10

to
1
2
3
4
5
6
7
8
9
10
1
2
3
4
5
6
7
8
9
10

please help?

thanks,

moving data from numerous colums into one colum
 

You may wish to start a new thread if a new question.
--
John C


"teresa" wrote:

Guys no need to argue,

i got it sorted,

Ive another problem if anyone would like to help with that?

thanks,

teresa


"John C" wrote:

No. If you have been using excel for long, which I am assuming you have, then
you should be the first to know that there are MANY ways to achieve the same
result. Just because you use a macro, does not make it the 'proper' way.
--
John C


"Don Guillett" wrote:

You are absolutely correct. But then, so am I.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"John C" <johnc@stateofdenial wrote in message
...
Formula solutions have been given. Some people aren't comfortable with
macros, or VBA for that matter. To say that your way is the 'proper' way,
and
that other ways are subsequently improper is both arrogant and pompous.
The
macro way is typically cleaner, but if it is a one time rearrangement, for
someone to go through all the learning of macros, and operation thereof I
think is a bit much.
--
John C


"Don Guillett" wrote:


Please don't shout. I can hear well despite my age. I would NOT use a
formula for this. You should learn how to do it properly.

If you're new to macros, you may want to read David McRitchie's
intro
at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"teresa" wrote in message
...
HOW CAN YOU DO THIS AS A FORMULA??
SORRY THIS HAS CONFUSED ME MORE?

THANKS,

TERESA


"Don Guillett" wrote:

Sub transposerowstocola()
lr = Cells(rows.Count, 1).End(xlUp).Row
For i = 1 To lr
lc = Cells(i, Columns.Count).End(xlToLeft).Column
dlr = Cells(rows.Count, 1).End(xlUp).Row + 1
Range(Cells(i, 1), Cells(i, lc)).Copy
Cells(dlr, 1).PasteSpecial Paste:=xlPasteAll, Transpose:=True
Next i
rows("1:" & lr).Delete
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"teresa" wrote in message
...
Hi,

I have to move the data from numerous columns into one colum as a
list,
how can i do this?

eg
1 2 3 4 5 6 7 8 9 10
1 2 3 4 5 6 7 8 9 10

to
1
2
3
4
5
6
7
8
9
10
1
2
3
4
5
6
7
8
9
10

please help?

thanks,

moving data from numerous colums into one colum
 

You could enter this formula wherever you wish, and copy down as needed:

=INDEX($A$1:$J$2,MOD(ROWS($1:1)-1,2)+1,ROWS($1:2)/2)

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"teresa" wrote in message
...
Guys no need to argue,

i got it sorted,

Ive another problem if anyone would like to help with that?

thanks,

teresa


"John C" wrote:

No. If you have been using excel for long, which I am assuming you have,
then
you should be the first to know that there are MANY ways to achieve the
same
result. Just because you use a macro, does not make it the 'proper' way.
--
John C


"Don Guillett" wrote:

You are absolutely correct. But then, so am I.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"John C" <johnc@stateofdenial wrote in message
...
Formula solutions have been given. Some people aren't comfortable with
macros, or VBA for that matter. To say that your way is the 'proper'
way,
and
that other ways are subsequently improper is both arrogant and
pompous.
The
macro way is typically cleaner, but if it is a one time rearrangement,
for
someone to go through all the learning of macros, and operation
thereof I
think is a bit much.
--
John C


"Don Guillett" wrote:


Please don't shout. I can hear well despite my age. I would NOT use a
formula for this. You should learn how to do it properly.

If you're new to macros, you may want to read David McRitchie's
intro
at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"teresa" wrote in message
...
HOW CAN YOU DO THIS AS A FORMULA??
SORRY THIS HAS CONFUSED ME MORE?

THANKS,

TERESA


"Don Guillett" wrote:

Sub transposerowstocola()
lr = Cells(rows.Count, 1).End(xlUp).Row
For i = 1 To lr
lc = Cells(i, Columns.Count).End(xlToLeft).Column
dlr = Cells(rows.Count, 1).End(xlUp).Row + 1
Range(Cells(i, 1), Cells(i, lc)).Copy
Cells(dlr, 1).PasteSpecial Paste:=xlPasteAll, Transpose:=True
Next i
rows("1:" & lr).Delete
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"teresa" wrote in message
...
Hi,

I have to move the data from numerous columns into one colum as
a
list,
how can i do this?

eg
1 2 3 4 5 6 7 8 9 10
1 2 3 4 5 6 7 8 9 10

to
1
2
3
4
5
6
7
8
9
10
1
2
3
4
5
6
7
8
9
10

please help?

thanks,

moving data from numerous colums into one colum
 

Of course, I did read them.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"John C" <johnc@stateofdenial wrote in message
...
As has been stated by several posters. Of course, you obviously didn't
read
them, or you would have noted that already.
--
John C


"Don Guillett" wrote:


Because I am pompous and arrogant, I say that it IS the proper way IN
THIS
CASE. I think it absolutely silly to burden a project with unnecessary
calculations and other formula overhead. Of course, the formula solution
could be used but the results should then be converted to values.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"John C" <johnc@stateofdenial wrote in message
...
No. If you have been using excel for long, which I am assuming you
have,
then
you should be the first to know that there are MANY ways to achieve the
same
result. Just because you use a macro, does not make it the 'proper'
way.
--
John C


"Don Guillett" wrote:

You are absolutely correct. But then, so am I.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"John C" <johnc@stateofdenial wrote in message
...
Formula solutions have been given. Some people aren't comfortable
with
macros, or VBA for that matter. To say that your way is the 'proper'
way,
and
that other ways are subsequently improper is both arrogant and
pompous.
The
macro way is typically cleaner, but if it is a one time
rearrangement,
for
someone to go through all the learning of macros, and operation
thereof
I
think is a bit much.
--
John C


"Don Guillett" wrote:


Please don't shout. I can hear well despite my age. I would NOT use
a
formula for this. You should learn how to do it properly.

If you're new to macros, you may want to read David
McRitchie's
intro
at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"teresa" wrote in message
...
HOW CAN YOU DO THIS AS A FORMULA??
SORRY THIS HAS CONFUSED ME MORE?

THANKS,

TERESA


"Don Guillett" wrote:

Sub transposerowstocola()
lr = Cells(rows.Count, 1).End(xlUp).Row
For i = 1 To lr
lc = Cells(i, Columns.Count).End(xlToLeft).Column
dlr = Cells(rows.Count, 1).End(xlUp).Row + 1
Range(Cells(i, 1), Cells(i, lc)).Copy
Cells(dlr, 1).PasteSpecial Paste:=xlPasteAll, Transpose:=True
Next i
rows("1:" & lr).Delete
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"teresa" wrote in message
...
Hi,

I have to move the data from numerous columns into one colum
as a
list,
how can i do this?

eg
1 2 3 4 5 6 7 8 9 10
1 2 3 4 5 6 7 8 9 10

to
1
2
3
4
5
6
7
8
9
10
1
2
3
4
5
6
7
8
9
10

please help?

thanks,

moving data from numerous colums into one colum
 

Okay, and I am SURE you have taken into account with your macro that the
there are no other people that may need to use this macro, and that even if
there are that the macro security isn't set too high, and even if it is, then
how for the OP to create a digital signature, and add that digital signature
to anyone else's profile as a trusted source. I am sure this was all taken
into account, correct? You mention the 'proper' way, well, this would be the
'proper way', wouldn't it?
--
John C


"Don Guillett" wrote:


Of course, I did read them.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"John C" <johnc@stateofdenial wrote in message
...
As has been stated by several posters. Of course, you obviously didn't
read
them, or you would have noted that already.
--
John C


"Don Guillett" wrote:


Because I am pompous and arrogant, I say that it IS the proper way IN
THIS
CASE. I think it absolutely silly to burden a project with unnecessary
calculations and other formula overhead. Of course, the formula solution
could be used but the results should then be converted to values.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"John C" <johnc@stateofdenial wrote in message
...
No. If you have been using excel for long, which I am assuming you
have,
then
you should be the first to know that there are MANY ways to achieve the
same
result. Just because you use a macro, does not make it the 'proper'
way.
--
John C


"Don Guillett" wrote:

You are absolutely correct. But then, so am I.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"John C" <johnc@stateofdenial wrote in message
...
Formula solutions have been given. Some people aren't comfortable
with
macros, or VBA for that matter. To say that your way is the 'proper'
way,
and
that other ways are subsequently improper is both arrogant and
pompous.
The
macro way is typically cleaner, but if it is a one time
rearrangement,
for
someone to go through all the learning of macros, and operation
thereof
I
think is a bit much.
--
John C


"Don Guillett" wrote:


Please don't shout. I can hear well despite my age. I would NOT use
a
formula for this. You should learn how to do it properly.

If you're new to macros, you may want to read David
McRitchie's
intro
at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"teresa" wrote in message
...
HOW CAN YOU DO THIS AS A FORMULA??
SORRY THIS HAS CONFUSED ME MORE?

THANKS,

TERESA


"Don Guillett" wrote:

Sub transposerowstocola()
lr = Cells(rows.Count, 1).End(xlUp).Row
For i = 1 To lr
lc = Cells(i, Columns.Count).End(xlToLeft).Column
dlr = Cells(rows.Count, 1).End(xlUp).Row + 1
Range(Cells(i, 1), Cells(i, lc)).Copy
Cells(dlr, 1).PasteSpecial Paste:=xlPasteAll, Transpose:=True
Next i
rows("1:" & lr).Delete
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"teresa" wrote in message
...
Hi,

I have to move the data from numerous columns into one colum
as a
list,
how can i do this?

eg
1 2 3 4 5 6 7 8 9 10
1 2 3 4 5 6 7 8 9 10

to
1
2
3
4
5
6
7
8
9
10
1
2
3
4
5
6
7
8
9
10

please help?

thanks,

moving data from numerous colums into one colum
 

Geeez. OP mentioned none of your rant.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"John C" <johnc@stateofdenial wrote in message
...
Okay, and I am SURE you have taken into account with your macro that the
there are no other people that may need to use this macro, and that even
if
there are that the macro security isn't set too high, and even if it is,
then
how for the OP to create a digital signature, and add that digital
signature
to anyone else's profile as a trusted source. I am sure this was all taken
into account, correct? You mention the 'proper' way, well, this would be
the
'proper way', wouldn't it?
--
John C


"Don Guillett" wrote:


Of course, I did read them.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"John C" <johnc@stateofdenial wrote in message
...
As has been stated by several posters. Of course, you obviously didn't
read
them, or you would have noted that already.
--
John C


"Don Guillett" wrote:


Because I am pompous and arrogant, I say that it IS the proper way IN
THIS
CASE. I think it absolutely silly to burden a project with unnecessary
calculations and other formula overhead. Of course, the formula
solution
could be used but the results should then be converted to values.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"John C" <johnc@stateofdenial wrote in message
...
No. If you have been using excel for long, which I am assuming you
have,
then
you should be the first to know that there are MANY ways to achieve
the
same
result. Just because you use a macro, does not make it the 'proper'
way.
--
John C


"Don Guillett" wrote:

You are absolutely correct. But then, so am I.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"John C" <johnc@stateofdenial wrote in message
...
Formula solutions have been given. Some people aren't comfortable
with
macros, or VBA for that matter. To say that your way is the
'proper'
way,
and
that other ways are subsequently improper is both arrogant and
pompous.
The
macro way is typically cleaner, but if it is a one time
rearrangement,
for
someone to go through all the learning of macros, and operation
thereof
I
think is a bit much.
--
John C


"Don Guillett" wrote:


Please don't shout. I can hear well despite my age. I would NOT
use
a
formula for this. You should learn how to do it properly.

If you're new to macros, you may want to read David
McRitchie's
intro
at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"teresa" wrote in message
...
HOW CAN YOU DO THIS AS A FORMULA??
SORRY THIS HAS CONFUSED ME MORE?

THANKS,

TERESA


"Don Guillett" wrote:

Sub transposerowstocola()
lr = Cells(rows.Count, 1).End(xlUp).Row
For i = 1 To lr
lc = Cells(i, Columns.Count).End(xlToLeft).Column
dlr = Cells(rows.Count, 1).End(xlUp).Row + 1
Range(Cells(i, 1), Cells(i, lc)).Copy
Cells(dlr, 1).PasteSpecial Paste:=xlPasteAll, Transpose:=True
Next i
rows("1:" & lr).Delete
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"teresa" wrote in message
...
Hi,

I have to move the data from numerous columns into one
colum
as a
list,
how can i do this?

eg
1 2 3 4 5 6 7 8 9 10
1 2 3 4 5 6 7 8 9 10

to
1
2
3
4
5
6
7
8
9
10
1
2
3
4
5
6
7
8
9
10

please help?

thanks,

moving data from numerous colums into one colum
 

You are correct. But you are the one that said that your way is the 'proper'
way. Well, if you are going to be proper, don't you think you be completely
proper, and not just be proper, sort of?
--
John C


"Don Guillett" wrote:

Geeez. OP mentioned none of your rant.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"John C" <johnc@stateofdenial wrote in message
...
Okay, and I am SURE you have taken into account with your macro that the
there are no other people that may need to use this macro, and that even
if
there are that the macro security isn't set too high, and even if it is,
then
how for the OP to create a digital signature, and add that digital
signature
to anyone else's profile as a trusted source. I am sure this was all taken
into account, correct? You mention the 'proper' way, well, this would be
the
'proper way', wouldn't it?
--
John C


"Don Guillett" wrote:


Of course, I did read them.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"John C" <johnc@stateofdenial wrote in message
...
As has been stated by several posters. Of course, you obviously didn't
read
them, or you would have noted that already.
--
John C


"Don Guillett" wrote:


Because I am pompous and arrogant, I say that it IS the proper way IN
THIS
CASE. I think it absolutely silly to burden a project with unnecessary
calculations and other formula overhead. Of course, the formula
solution
could be used but the results should then be converted to values.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"John C" <johnc@stateofdenial wrote in message
...
No. If you have been using excel for long, which I am assuming you
have,
then
you should be the first to know that there are MANY ways to achieve
the
same
result. Just because you use a macro, does not make it the 'proper'
way.
--
John C


"Don Guillett" wrote:

You are absolutely correct. But then, so am I.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"John C" <johnc@stateofdenial wrote in message
...
Formula solutions have been given. Some people aren't comfortable
with
macros, or VBA for that matter. To say that your way is the
'proper'
way,
and
that other ways are subsequently improper is both arrogant and
pompous.
The
macro way is typically cleaner, but if it is a one time
rearrangement,
for
someone to go through all the learning of macros, and operation
thereof
I
think is a bit much.
--
John C


"Don Guillett" wrote:


Please don't shout. I can hear well despite my age. I would NOT
use
a
formula for this. You should learn how to do it properly.

If you're new to macros, you may want to read David
McRitchie's
intro
at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"teresa" wrote in message
...
HOW CAN YOU DO THIS AS A FORMULA??
SORRY THIS HAS CONFUSED ME MORE?

THANKS,

TERESA


"Don Guillett" wrote:

Sub transposerowstocola()
lr = Cells(rows.Count, 1).End(xlUp).Row
For i = 1 To lr
lc = Cells(i, Columns.Count).End(xlToLeft).Column
dlr = Cells(rows.Count, 1).End(xlUp).Row + 1
Range(Cells(i, 1), Cells(i, lc)).Copy
Cells(dlr, 1).PasteSpecial Paste:=xlPasteAll, Transpose:=True
Next i
rows("1:" & lr).Delete
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"teresa" wrote in message
...
Hi,

I have to move the data from numerous columns into one
colum
as a
list,
how can i do this?

eg
1 2 3 4 5 6 7 8 9 10
1 2 3 4 5 6 7 8 9 10

to
1
2
3
4
5
6
7
8
9
10
1
2
3
4
5
6
7
8
9
10

please help?

thanks,