D

#### Daniel

I have Excel 2007, I have a spreadsheet with 38 columns with product names

in an average of

50 rows in each column, one name per cell.

I need to put all of these products into one column.

How can I do that automatically.

thanks

Daniel

You are using an out of date browser. It may not display this or other websites correctly.

You should upgrade or use an alternative browser.

You should upgrade or use an alternative browser.

D

I have Excel 2007, I have a spreadsheet with 38 columns with product names

in an average of

50 rows in each column, one name per cell.

I need to put all of these products into one column.

How can I do that automatically.

thanks

Daniel

D

Give a try to :

Sub test()

Dim c As Range, Sh As Worksheet

Set Sh = ActiveSheet

Sheets.Add before:=Sheets(1)

With Sh

For i = 1 To 38

For Each c In Range(.Cells(1, i), .Cells(65536, i).End(xlUp))

Range("A65536").End(xlUp).Offset(1) = c

Next c

Next i

End With

End Sub

HTH

Daniel

D

try to work it out and hopefully learn something in the process.

regards

Daniel

C

There are any number of ways to do this. Here are two: one using

worksheet formulas and one using VBA code.

FORMULA:

Suppose your data table begins in cell B7 and each column has (at

most) 50 rows. Also, assume you want your single column list to begin

at cell R24. Enter the following formula in R24 and copy down to about

row 2000 (or far enough so that all values in the data table are

accounted for):

=OFFSET($B$7,MOD(ROW()-ROW($R$24),50),TRUNC((ROW()-ROW($R$24))/50),1,1)

This will create a single column that contains all the data within the

data table. However, it assumes that all columns have the same number

of elements, so two things happen: blanks will be present in the

single column list where there are blanks in the data table, and any

column with more that 50 rows will be truncated in the column list.

VBA CODE:

If you prefer a VBA approach, use code like the following. Change the

Const values to meet your needs.

Sub AAA()

Dim Dest As Range

Dim R As Range

Dim WS As Worksheet

'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

' change constants to fit your need

'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

Const LIST_START_ROW = 21 ' result list begins in this row

Const LIST_START_COLUMN = "D" ' result list begins in this

column

Const TABLE_START_ROW = 7 ' data table starts in this row

Const TABLE_START_COLUMN = "B" ' data table starts in this column

Const TABLE_END_COLUMN = "J" ' data table ends in this column

Const SHEET_NAME = "Sheet1" ' result list and data table

reside on this table

'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

Set WS = Worksheets(SHEET_NAME)

Set Dest = WS.Cells(LIST_START_ROW, LIST_START_COLUMN)

Set R = WS.Cells(TABLE_START_ROW, TABLE_START_COLUMN)

Do Until R.Column > Cells(1, TABLE_END_COLUMN).Column

Dest.Value = R.Value

Set R = R(2, 1)

If R.Value = vbNullString Then

Set R = R(1, 2).EntireColumn.Cells(TABLE_START_ROW, 1)

End If

Set Dest = Dest(2, 1)

Loop

End Sub

This code assumes that while the coluimns may have varying length, the

presence of a blank cell marks the end of each column.

Cordially,

Chip Pearson

Microsoft MVP

Excel Product Group

Pearson Software Consulting, LLC

www.cpearson.com

(email on web site)

D

Thanks Chip, appreciate your advice, and it worked well.

regards

Daniel

regards

Daniel

S

A slightly different approach, if order doesn't matter, which assumes that

the most items in any column will be 59, change this to any number you want,

it assumes that the range C1:AN59 encompasses all the data, blank or not.

Enter this in cell A1 and copy it down.

=INDEX($C$1:$AN$59,MOD(ROW(A1),59),ROUNDUP(ROW(A1)/59,0))

Select all these formulas and choose Copy, then choose Edit, Paste Special,

Values. Sort the results Descending. All the zeros will be at the bottom of

the list.

Cheers,

Shane Devenshire

D

thanks to all who gave advice.

I have learned a great deal.

regards

Daniel

M

By question is, which is the correct syntaxis for the setting an instruction that clears the original range where I copied the data?

The syntax I'm using to clear the range is

[Range("TABLE_START_ROW", "TABLE_END_COLUMN").Clear]

but obviously it is wrong.

Hope you can help me, thanks a lot.

Chip Pearson wrote:

Re: Moving data from many columns to a single column

14-nov-08

Daniel,

There are any number of ways to do this. Here are two: one usin

worksheet formulas and one using VBA code

FORMULA

Suppose your data table begins in cell B7 and each column has (a

most) 50 rows. Also, assume you want your single column list to begi

at cell R24. Enter the following formula in R24 and copy down to abou

row 2000 (or far enough so that all values in the data table ar

accounted for)

=OFFSET($B$7,MOD(ROW()-ROW($R$24),50),TRUNC((ROW()-ROW($R$24))/50),1,1

This will create a single column that contains all the data within th

data table. However, it assumes that all columns have the same numbe

of elements, so two things happen: blanks will be present in th

single column list where there are blanks in the data table, and an

column with more that 50 rows will be truncated in the column list

VBA CODE

If you prefer a VBA approach, use code like the following. Change th

Const values to meet your needs

Sub AAA(

Dim Dest As Rang

Dim R As Rang

Dim WS As Workshee

'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

' change constants to fit your nee

'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

Const LIST_START_ROW = 21 ' result list begins in this ro

Const LIST_START_COLUMN = "D" ' result list begins in thi

colum

Const TABLE_START_ROW = 7 ' data table starts in this ro

Const TABLE_START_COLUMN = "B" ' data table starts in this colum

Const TABLE_END_COLUMN = "J" ' data table ends in this colum

Const SHEET_NAME = "Sheet1" ' result list and data tabl

reside on this tabl

'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

Set WS = Worksheets(SHEET_NAME

Set Dest = WS.Cells(LIST_START_ROW, LIST_START_COLUMN

Set R = WS.Cells(TABLE_START_ROW, TABLE_START_COLUMN

Do Until R.Column > Cells(1, TABLE_END_COLUMN).Colum

Dest.Value = R.Valu

Set R = R(2, 1

If R.Value = vbNullString The

Set R = R(1, 2).EntireColumn.Cells(TABLE_START_ROW, 1

End I

Set Dest = Dest(2, 1

Loo

End Su

This code assumes that while the coluimns may have varying length, th

presence of a blank cell marks the end of each column

Cordially

Chip Pearso

Microsoft MVP

Excel Product Grou

Pearson Software Consulting, LL

www.cpearson.co

(email on web site

Previous Posts In This Thread:

On viernes, 14 de noviembre de 2008 01:01 p.m.

Daniel wrote:

Moving data from many columns to a single column

H

I have Excel 2007, I have a spreadsheet with 38 columns with product names

in an average o

50 rows in each column, one name per cell

I need to put all of these products into one column

How can I do that automatically

thank

Daniel

On viernes, 14 de noviembre de 2008 02:03 p.m.

Daniel.C wrote:

Re: Moving data from many columns to a single column

Hi

Give a try to

Sub test(

Dim c As Range, Sh As Workshee

Set Sh = ActiveShee

Sheets.Add before:=Sheets(1

With S

For i = 1 To 3

For Each c In Range(.Cells(1, i), .Cells(65536, i).End(xlUp)

Range("A65536").End(xlUp).Offset(1) =

Next

Next

End Wit

End Su

HT

Daniel

On viernes, 14 de noviembre de 2008 02:14 p.m.

Daniel wrote:

Re: Moving data from many columns to a single column

Thanks for your help, it is a bit (a lot) over my head but I wil

try to work it out and hopefully learn something in the process

regards

Daniel

On viernes, 14 de noviembre de 2008 03:34 p.m.

Chip Pearson wrote:

Re: Moving data from many columns to a single column

Daniel,

There are any number of ways to do this. Here are two: one using

worksheet formulas and one using VBA code.

FORMULA:

Suppose your data table begins in cell B7 and each column has (at

most) 50 rows. Also, assume you want your single column list to begin

at cell R24. Enter the following formula in R24 and copy down to about

row 2000 (or far enough so that all values in the data table are

accounted for):

=OFFSET($B$7,MOD(ROW()-ROW($R$24),50),TRUNC((ROW()-ROW($R$24))/50),1,1)

This will create a single column that contains all the data within the

data table. However, it assumes that all columns have the same number

of elements, so two things happen: blanks will be present in the

single column list where there are blanks in the data table, and any

column with more that 50 rows will be truncated in the column list.

VBA CODE:

If you prefer a VBA approach, use code like the following. Change the

Const values to meet your needs.

Sub AAA()

Dim Dest As Range

Dim R As Range

Dim WS As Worksheet

'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

' change constants to fit your need

'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

Const LIST_START_ROW = 21 ' result list begins in this row

Const LIST_START_COLUMN = "D" ' result list begins in this

column

Const TABLE_START_ROW = 7 ' data table starts in this row

Const TABLE_START_COLUMN = "B" ' data table starts in this column

Const TABLE_END_COLUMN = "J" ' data table ends in this column

Const SHEET_NAME = "Sheet1" ' result list and data table

reside on this table

'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

Set WS = Worksheets(SHEET_NAME)

Set Dest = WS.Cells(LIST_START_ROW, LIST_START_COLUMN)

Set R = WS.Cells(TABLE_START_ROW, TABLE_START_COLUMN)

Do Until R.Column > Cells(1, TABLE_END_COLUMN).Column

Dest.Value = R.Value

Set R = R(2, 1)

If R.Value = vbNullString Then

Set R = R(1, 2).EntireColumn.Cells(TABLE_START_ROW, 1)

End If

Set Dest = Dest(2, 1)

Loop

End Sub

This code assumes that while the coluimns may have varying length, the

presence of a blank cell marks the end of each column.

Cordially,

Chip Pearson

Microsoft MVP

Excel Product Group

Pearson Software Consulting, LLC

www.cpearson.com

(email on web site)

On viernes, 14 de noviembre de 2008 09:39 p.m.

Daniel wrote:

Re: Moving data from many columns to a single column

Thanks Chip, appreciate your advice, and it worked well.

regards

Daniel

On s?bado, 15 de noviembre de 2008 12:35 a.m.

ShaneDevenshir wrote:

RE: Moving data from many columns to a single column

Hi,

A slightly different approach, if order doesn't matter, which assumes that

the most items in any column will be 59, change this to any number you want,

it assumes that the range C1:AN59 encompasses all the data, blank or not.

Enter this in cell A1 and copy it down.

=INDEX($C$1:$AN$59,MOD(ROW(A1),59),ROUNDUP(ROW(A1)/59,0))

Select all these formulas and choose Copy, then choose Edit, Paste Special,

Values. Sort the results Descending. All the zeros will be at the bottom of

the list.

Cheers,

Shane Devenshire

:

On s?bado, 15 de noviembre de 2008 07:47 p.m.

Daniel wrote:

Re: Moving data from many columns to a single column

great, also worked well and was probably the simplest,

thanks to all who gave advice.

I have learned a great deal.

regards

Daniel

message

EggHeadCafe - Software Developer Portal of Choice

Working with Client Side Xml Data Islands from Server-Side ASP.NET code

http://www.eggheadcafe.com/tutorial...a9-e41f967e573b/working-with-client-side.aspx

M

Greetings!

Sub AAA()

Dim Dest As Range

Dim R As Range

Dim WS As Worksheet

Dim LIST_START_ROW As Single

Dim LIST_START_COLUMN As String

Dim TABLE_START_ROW As Single

Dim TABLE_START_COLUMN As String

Dim TABLE_END_COLUMN As String

LIST_START_ROW = InputBox("?En qu? fila pegar resultados?")

LIST_START_COLUMN = InputBox("?En qu? columna pegar resultads?")

TABLE_START_ROW = InputBox("?En qu? FILA empieza la tabla de datos?")

TABLE_START_COLUMN = InputBox("?En qu? COLUMNA empieza la tabla de datos?")

TABLE_END_COLUMN = InputBox("?En qu? COLUMNA TERMINA la tabla de datos?")

'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

' change constants to fit your need

'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

Const SHEET_NAME = "Sheet1" ' result list and data table reside on this table

'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

Set WS = Worksheets(SHEET_NAME)

Set Dest = WS.Cells(LIST_START_ROW, LIST_START_COLUMN)

Set R = WS.Cells(TABLE_START_ROW, TABLE_START_COLUMN)

Do Until R.Column > Cells(1, TABLE_END_COLUMN).Column

Dest.Value = R.Value

Set R = R(2, 1)

If R.Value = vbNullString Then

Set R = R(1, 2).EntireColumn.Cells(TABLE_START_ROW, 1)

End If

Set Dest = Dest(2, 1)

Loop

End Sub

Chip Pearson wrote:

Re: Moving data from many columns to a single column

14-nov-08

Daniel,

There are any number of ways to do this. Here are two: one using

worksheet formulas and one using VBA code.

FORMULA:

Suppose your data table begins in cell B7 and each column has (at

most) 50 rows. Also, assume you want your single column list to begin

at cell R24. Enter the following formula in R24 and copy down to about

row 2000 (or far enough so that all values in the data table are

accounted for):

=OFFSET($B$7,MOD(ROW()-ROW($R$24),50),TRUNC((ROW()-ROW($R$24))/50),1,1)

This will create a single column that contains all the data within the

data table. However, it assumes that all columns have the same number

of elements, so two things happen: blanks will be present in the

single column list where there are blanks in the data table, and any

column with more that 50 rows will be truncated in the column list.

VBA CODE:

If you prefer a VBA approach, use code like the following. Change the

Const values to meet your needs.

Sub AAA()

Dim Dest As Range

Dim R As Range

Dim WS As Worksheet

'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

' change constants to fit your need

'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

Const LIST_START_ROW = 21 ' result list begins in this row

Const LIST_START_COLUMN = "D" ' result list begins in this

column

Const TABLE_START_ROW = 7 ' data table starts in this row

Const TABLE_START_COLUMN = "B" ' data table starts in this column

Const TABLE_END_COLUMN = "J" ' data table ends in this column

Const SHEET_NAME = "Sheet1" ' result list and data table

reside on this table

'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

Set WS = Worksheets(SHEET_NAME)

Set Dest = WS.Cells(LIST_START_ROW, LIST_START_COLUMN)

Set R = WS.Cells(TABLE_START_ROW, TABLE_START_COLUMN)

Do Until R.Column > Cells(1, TABLE_END_COLUMN).Column

Dest.Value = R.Value

Set R = R(2, 1)

If R.Value = vbNullString Then

Set R = R(1, 2).EntireColumn.Cells(TABLE_START_ROW, 1)

End If

Set Dest = Dest(2, 1)

Loop

End Sub

This code assumes that while the coluimns may have varying length, the

presence of a blank cell marks the end of each column.

Cordially,

Chip Pearson

Microsoft MVP

Excel Product Group

Pearson Software Consulting, LLC

www.cpearson.com

(email on web site)

Previous Posts In This Thread:

On viernes, 14 de noviembre de 2008 01:01 p.m.

Daniel wrote:

Moving data from many columns to a single column

Hi

I have Excel 2007, I have a spreadsheet with 38 columns with product names

in an average of

50 rows in each column, one name per cell.

I need to put all of these products into one column.

How can I do that automatically.

thanks

Daniel

On viernes, 14 de noviembre de 2008 02:03 p.m.

Daniel.C wrote:

Re: Moving data from many columns to a single column

Hi.

Give a try to :

Sub test()

Dim c As Range, Sh As Worksheet

Set Sh = ActiveSheet

Sheets.Add before:=Sheets(1)

With Sh

For i = 1 To 38

For Each c In Range(.Cells(1, i), .Cells(65536, i).End(xlUp))

Range("A65536").End(xlUp).Offset(1) = c

Next c

Next i

End With

End Sub

HTH

Daniel

On viernes, 14 de noviembre de 2008 02:14 p.m.

Daniel wrote:

Re: Moving data from many columns to a single column

Thanks for your help, it is a bit (a lot) over my head but I will

try to work it out and hopefully learn something in the process.

regards

Daniel

On viernes, 14 de noviembre de 2008 03:34 p.m.

Chip Pearson wrote:

Re: Moving data from many columns to a single column

Daniel,

There are any number of ways to do this. Here are two: one using

worksheet formulas and one using VBA code.

FORMULA:

Suppose your data table begins in cell B7 and each column has (at

most) 50 rows. Also, assume you want your single column list to begin

at cell R24. Enter the following formula in R24 and copy down to about

row 2000 (or far enough so that all values in the data table are

accounted for):

=OFFSET($B$7,MOD(ROW()-ROW($R$24),50),TRUNC((ROW()-ROW($R$24))/50),1,1)

This will create a single column that contains all the data within the

data table. However, it assumes that all columns have the same number

of elements, so two things happen: blanks will be present in the

single column list where there are blanks in the data table, and any

column with more that 50 rows will be truncated in the column list.

VBA CODE:

If you prefer a VBA approach, use code like the following. Change the

Const values to meet your needs.

Sub AAA()

Dim Dest As Range

Dim R As Range

Dim WS As Worksheet

'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

' change constants to fit your need

'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

Const LIST_START_ROW = 21 ' result list begins in this row

Const LIST_START_COLUMN = "D" ' result list begins in this

column

Const TABLE_START_ROW = 7 ' data table starts in this row

Const TABLE_START_COLUMN = "B" ' data table starts in this column

Const TABLE_END_COLUMN = "J" ' data table ends in this column

Const SHEET_NAME = "Sheet1" ' result list and data table

reside on this table

'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

Set WS = Worksheets(SHEET_NAME)

Set Dest = WS.Cells(LIST_START_ROW, LIST_START_COLUMN)

Set R = WS.Cells(TABLE_START_ROW, TABLE_START_COLUMN)

Do Until R.Column > Cells(1, TABLE_END_COLUMN).Column

Dest.Value = R.Value

Set R = R(2, 1)

If R.Value = vbNullString Then

Set R = R(1, 2).EntireColumn.Cells(TABLE_START_ROW, 1)

End If

Set Dest = Dest(2, 1)

Loop

End Sub

This code assumes that while the coluimns may have varying length, the

presence of a blank cell marks the end of each column.

Cordially,

Chip Pearson

Microsoft MVP

Excel Product Group

Pearson Software Consulting, LLC

www.cpearson.com

(email on web site)

On viernes, 14 de noviembre de 2008 09:39 p.m.

Daniel wrote:

Re: Moving data from many columns to a single column

Thanks Chip, appreciate your advice, and it worked well.

regards

Daniel

On s?bado, 15 de noviembre de 2008 12:35 a.m.

ShaneDevenshir wrote:

RE: Moving data from many columns to a single column

Hi,

A slightly different approach, if order doesn't matter, which assumes that

the most items in any column will be 59, change this to any number you want,

it assumes that the range C1:AN59 encompasses all the data, blank or not.

Enter this in cell A1 and copy it down.

=INDEX($C$1:$AN$59,MOD(ROW(A1),59),ROUNDUP(ROW(A1)/59,0))

Select all these formulas and choose Copy, then choose Edit, Paste Special,

Values. Sort the results Descending. All the zeros will be at the bottom of

the list.

Cheers,

Shane Devenshire

:

On s?bado, 15 de noviembre de 2008 07:47 p.m.

Daniel wrote:

Re: Moving data from many columns to a single column

great, also worked well and was probably the simplest,

thanks to all who gave advice.

I have learned a great deal.

regards

Daniel

message

On jueves, 15 de octubre de 2009 02:43 p.m.

Manuel Carrillo wrote:

And how to clear the range that were copied?

Hi, I saw your code, it is great, also I modified it to select where to put the single column and frome where to take the values, all this just wiht input boxes.

By question is, which is the correct syntaxis for the setting an instruction that clears the original range where I copied the data?

The syntax I'm using to clear the range is

[Range("TABLE_START_ROW", "TABLE_END_COLUMN").Clear]

but obviously it is wrong.

Hope you can help me, thanks a lot.

EggHeadCafe - Software Developer Portal of Choice

ASP.NET Dynamic Progress Page

http://www.eggheadcafe.com/tutorial...578-9c4fca97670b/aspnet-dynamic-progress.aspx

P

I think this is what you need:

Range(Cells(TABLE_START_ROW, TABLE_START_COLUMN), Cells(Rows.Count,

TABEL_END_ROW).End(xlUp)).Clear

Regards,

Per

"Manuel Carrillo" skrev i meddelelsen

Hi, I saw your code, it is great, also I modified it to select where to

put the single column and frome where to take the values, all this just

wiht input boxes.

By question is, which is the correct syntaxis for the setting an

instruction that clears the original range where I copied the data?

The syntax I'm using to clear the range is

[Range("TABLE_START_ROW", "TABLE_END_COLUMN").Clear]

but obviously it is wrong.

Hope you can help me, thanks a lot.

Chip Pearson wrote:

Re: Moving data from many columns to a single column

14-nov-08

Daniel,

There are any number of ways to do this. Here are two: one using

worksheet formulas and one using VBA code.

FORMULA:

Suppose your data table begins in cell B7 and each column has (at

most) 50 rows. Also, assume you want your single column list to begin

at cell R24. Enter the following formula in R24 and copy down to about

row 2000 (or far enough so that all values in the data table are

accounted for):

=OFFSET($B$7,MOD(ROW()-ROW($R$24),50),TRUNC((ROW()-ROW($R$24))/50),1,1)

This will create a single column that contains all the data within the

data table. However, it assumes that all columns have the same number

of elements, so two things happen: blanks will be present in the

single column list where there are blanks in the data table, and any

column with more that 50 rows will be truncated in the column list.

VBA CODE:

If you prefer a VBA approach, use code like the following. Change the

Const values to meet your needs.

Sub AAA()

Dim Dest As Range

Dim R As Range

Dim WS As Worksheet

'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

' change constants to fit your need

'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

Const LIST_START_ROW = 21 ' result list begins in this row

Const LIST_START_COLUMN = "D" ' result list begins in this

column

Const TABLE_START_ROW = 7 ' data table starts in this row

Const TABLE_START_COLUMN = "B" ' data table starts in this column

Const TABLE_END_COLUMN = "J" ' data table ends in this column

Const SHEET_NAME = "Sheet1" ' result list and data table

reside on this table

'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

Set WS = Worksheets(SHEET_NAME)

Set Dest = WS.Cells(LIST_START_ROW, LIST_START_COLUMN)

Set R = WS.Cells(TABLE_START_ROW, TABLE_START_COLUMN)

Do Until R.Column > Cells(1, TABLE_END_COLUMN).Column

Dest.Value = R.Value

Set R = R(2, 1)

If R.Value = vbNullString Then

Set R = R(1, 2).EntireColumn.Cells(TABLE_START_ROW, 1)

End If

Set Dest = Dest(2, 1)

Loop

End Sub

This code assumes that while the coluimns may have varying length, the

presence of a blank cell marks the end of each column.

Cordially,

Chip Pearson

Microsoft MVP

Excel Product Group

Pearson Software Consulting, LLC

www.cpearson.com

(email on web site)

Previous Posts In This Thread:

On viernes, 14 de noviembre de 2008 01:01 p.m.

Daniel wrote:

Moving data from many columns to a single column

Hi

I have Excel 2007, I have a spreadsheet with 38 columns with product names

in an average of

50 rows in each column, one name per cell.

I need to put all of these products into one column.

How can I do that automatically.

thanks

Daniel

On viernes, 14 de noviembre de 2008 02:03 p.m.

Daniel.C wrote:

Re: Moving data from many columns to a single column

Hi.

Give a try to :

Sub test()

Dim c As Range, Sh As Worksheet

Set Sh = ActiveSheet

Sheets.Add before:=Sheets(1)

With Sh

For i = 1 To 38

For Each c In Range(.Cells(1, i), .Cells(65536, i).End(xlUp))

Range("A65536").End(xlUp).Offset(1) = c

Next c

Next i

End With

End Sub

HTH

Daniel

On viernes, 14 de noviembre de 2008 02:14 p.m.

Daniel wrote:

Re: Moving data from many columns to a single column

Thanks for your help, it is a bit (a lot) over my head but I will

try to work it out and hopefully learn something in the process.

regards

Daniel

On viernes, 14 de noviembre de 2008 03:34 p.m.

Chip Pearson wrote:

Re: Moving data from many columns to a single column

Daniel,

There are any number of ways to do this. Here are two: one using

worksheet formulas and one using VBA code.

FORMULA:

Suppose your data table begins in cell B7 and each column has (at

most) 50 rows. Also, assume you want your single column list to begin

at cell R24. Enter the following formula in R24 and copy down to about

row 2000 (or far enough so that all values in the data table are

accounted for):

=OFFSET($B$7,MOD(ROW()-ROW($R$24),50),TRUNC((ROW()-ROW($R$24))/50),1,1)

This will create a single column that contains all the data within the

data table. However, it assumes that all columns have the same number

of elements, so two things happen: blanks will be present in the

single column list where there are blanks in the data table, and any

column with more that 50 rows will be truncated in the column list.

VBA CODE:

If you prefer a VBA approach, use code like the following. Change the

Const values to meet your needs.

Sub AAA()

Dim Dest As Range

Dim R As Range

Dim WS As Worksheet

'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

' change constants to fit your need

'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

Const LIST_START_ROW = 21 ' result list begins in this row

Const LIST_START_COLUMN = "D" ' result list begins in this

column

Const TABLE_START_ROW = 7 ' data table starts in this row

Const TABLE_START_COLUMN = "B" ' data table starts in this column

Const TABLE_END_COLUMN = "J" ' data table ends in this column

Const SHEET_NAME = "Sheet1" ' result list and data table

reside on this table

'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

Set WS = Worksheets(SHEET_NAME)

Set Dest = WS.Cells(LIST_START_ROW, LIST_START_COLUMN)

Set R = WS.Cells(TABLE_START_ROW, TABLE_START_COLUMN)

Do Until R.Column > Cells(1, TABLE_END_COLUMN).Column

Dest.Value = R.Value

Set R = R(2, 1)

If R.Value = vbNullString Then

Set R = R(1, 2).EntireColumn.Cells(TABLE_START_ROW, 1)

End If

Set Dest = Dest(2, 1)

Loop

End Sub

This code assumes that while the coluimns may have varying length, the

presence of a blank cell marks the end of each column.

Cordially,

Chip Pearson

Microsoft MVP

Excel Product Group

Pearson Software Consulting, LLC

www.cpearson.com

(email on web site)

On viernes, 14 de noviembre de 2008 09:39 p.m.

Daniel wrote:

Re: Moving data from many columns to a single column

Thanks Chip, appreciate your advice, and it worked well.

regards

Daniel

On s?bado, 15 de noviembre de 2008 12:35 a.m.

ShaneDevenshir wrote:

RE: Moving data from many columns to a single column

Hi,

A slightly different approach, if order doesn't matter, which assumes that

the most items in any column will be 59, change this to any number you

want,

it assumes that the range C1:AN59 encompasses all the data, blank or not.

Enter this in cell A1 and copy it down.

=INDEX($C$1:$AN$59,MOD(ROW(A1),59),ROUNDUP(ROW(A1)/59,0))

Select all these formulas and choose Copy, then choose Edit, Paste

Special,

Values. Sort the results Descending. All the zeros will be at the bottom

of

the list.

Cheers,

Shane Devenshire

:

On s?bado, 15 de noviembre de 2008 07:47 p.m.

Daniel wrote:

Re: Moving data from many columns to a single column

great, also worked well and was probably the simplest,

thanks to all who gave advice.

I have learned a great deal.

regards

Daniel

message

EggHeadCafe - Software Developer Portal of Choice

Working with Client Side Xml Data Islands from Server-Side ASP.NET code

http://www.eggheadcafe.com/tutorial...a9-e41f967e573b/working-with-client-side.aspx