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,

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,

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,

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,

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,

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,

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.

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,

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,

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,

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,

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,