Hi there everyone

I have a table of data 10 rows and 10 columns wide with some blanks in
between. Is there a way to format it to a single column of data (order
doesn't matter). The output essentially just needs to be one column of data,
1 cell wide. If the blanks can be eliminated, nothing like it... if not, even
then I can live with that!!

Thanks guys,

Appreciate.

Raj.

Have a look at the code found at this google search thread.

http://snipurl.com/uxol


Gord Dibben MS Excel MVP

On Mon, 14 Aug 2006 13:09:02 -0700, Raj wrote:

Hi there everyone

I have a table of data 10 rows and 10 columns wide with some blanks in
between. Is there a way to format it to a single column of data (order
doesn't matter). The output essentially just needs to be one column of data,
1 cell wide. If the blanks can be eliminated, nothing like it... if not, even
then I can live with that!!

Thanks guys,

Appreciate.

Raj.

Thanks Gord... will try it out tonight and will let you know the outcome...
thanks again

Raj

"Raj" wrote:

Hi there everyone

I have a table of data 10 rows and 10 columns wide with some blanks in
between. Is there a way to format it to a single column of data (order
doesn't matter). The output essentially just needs to be one column of data,
1 cell wide. If the blanks can be eliminated, nothing like it... if not, even
then I can live with that!!

Thanks guys,

Appreciate.

Raj.

Hi again Gord

I tried copy pasting the macro to the ToolsMacroVB
editorSheet1InsertModule. Am I doing anything wrong? My skills are quite
basic with this? Would appreciate if you could explain a little more.

Thanks

Raj

"Raj" wrote:

Thanks Gord... will try it out tonight and will let you know the outcome...
thanks again

Raj

"Raj" wrote:

Hi there everyone

I have a table of data 10 rows and 10 columns wide with some blanks in
between. Is there a way to format it to a single column of data (order
doesn't matter). The output essentially just needs to be one column of data,
1 cell wide. If the blanks can be eliminated, nothing like it... if not, even
then I can live with that!!

Thanks guys,

Appreciate.

Raj.

Raj

Note that thewre are two versions of the macro at that google post.

Copy the second version which Bernie modified then.........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + R to open Project Explorer.

Find your workbook/project and select it.

Right-click and InsertModule. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run the macro by going to ToolMacroMacros.

You can also assign this macro to a button or a shortcut key combo.


Gord

On Tue, 15 Aug 2006 12:26:02 -0700, Raj wrote:

Hi again Gord

I tried copy pasting the macro to the ToolsMacroVB
editorSheet1InsertModule. Am I doing anything wrong? My skills are quite
basic with this? Would appreciate if you could explain a little more.

Thanks

Raj

"Raj" wrote:

Thanks Gord... will try it out tonight and will let you know the outcome...
thanks again

Raj

"Raj" wrote:

Hi there everyone

I have a table of data 10 rows and 10 columns wide with some blanks in
between. Is there a way to format it to a single column of data (order
doesn't matter). The output essentially just needs to be one column of data,
1 cell wide. If the blanks can be eliminated, nothing like it... if not, even
then I can live with that!!

Thanks guys,

Appreciate.

Raj.

Gord Dibben MS Excel MVP

Oh! Hi Gord... yes it's working to the point that my column has upto 3 rows
minimum. If my column has less than 3 rows, it's not picking up that
particular column of data into the new single column. Is there a way to fix
the macro?

Thanks

Raj

"Gord Dibben" wrote:

Raj

Note that thewre are two versions of the macro at that google post.

Copy the second version which Bernie modified then.........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + R to open Project Explorer.

Find your workbook/project and select it.

Right-click and InsertModule. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run the macro by going to ToolMacroMacros.

You can also assign this macro to a button or a shortcut key combo.


Gord

On Tue, 15 Aug 2006 12:26:02 -0700, Raj wrote:

Hi again Gord

I tried copy pasting the macro to the ToolsMacroVB
editorSheet1InsertModule. Am I doing anything wrong? My skills are quite
basic with this? Would appreciate if you could explain a little more.

Thanks

Raj

"Raj" wrote:

Thanks Gord... will try it out tonight and will let you know the outcome...
thanks again

Raj

"Raj" wrote:

Hi there everyone

I have a table of data 10 rows and 10 columns wide with some blanks in
between. Is there a way to format it to a single column of data (order
doesn't matter). The output essentially just needs to be one column of data,
1 cell wide. If the blanks can be eliminated, nothing like it... if not, even
then I can live with that!!

Thanks guys,

Appreciate.

Raj.

Gord Dibben MS Excel MVP

I cannot replicate your problem even when I have a column with only one entry in
row 1 and nothing else.

Answer is: I don't know what to fix


Gord

On Tue, 15 Aug 2006 14:06:02 -0700, Raj wrote:

Oh! Hi Gord... yes it's working to the point that my column has upto 3 rows
minimum. If my column has less than 3 rows, it's not picking up that
particular column of data into the new single column. Is there a way to fix
the macro?

Thanks

Raj

"Gord Dibben" wrote:

Raj

Note that thewre are two versions of the macro at that google post.

Copy the second version which Bernie modified then.........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + R to open Project Explorer.

Find your workbook/project and select it.

Right-click and InsertModule. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run the macro by going to ToolMacroMacros.

You can also assign this macro to a button or a shortcut key combo.


Gord

On Tue, 15 Aug 2006 12:26:02 -0700, Raj wrote:

Hi again Gord

I tried copy pasting the macro to the ToolsMacroVB
editorSheet1InsertModule. Am I doing anything wrong? My skills are quite
basic with this? Would appreciate if you could explain a little more.

Thanks

Raj

"Raj" wrote:

Thanks Gord... will try it out tonight and will let you know the outcome...
thanks again

Raj

"Raj" wrote:

Hi there everyone

I have a table of data 10 rows and 10 columns wide with some blanks in
between. Is there a way to format it to a single column of data (order
doesn't matter). The output essentially just needs to be one column of data,
1 cell wide. If the blanks can be eliminated, nothing like it... if not, even
then I can live with that!!

Thanks guys,

Appreciate.

Raj.

Gord Dibben MS Excel MVP


Gord Dibben MS Excel MVP

Hi Gord

Sorry, for the delay in responding. I understand what you say. If you have a
table in excel where the first row of data has the least number of columns
than the bottom ones, the macro is not picking up all the data. Something
like the following:
a b c d e
f g h i j k l m
n o p q r s t u v w x y z

In this case the macro is picking up only 15 of the above as the first row
has only 5.

Hope I could explain.

Thanks
Raj

"Gord Dibben" wrote:

I cannot replicate your problem even when I have a column with only one entry in
row 1 and nothing else.

Answer is: I don't know what to fix


Gord

On Tue, 15 Aug 2006 14:06:02 -0700, Raj wrote:

Oh! Hi Gord... yes it's working to the point that my column has upto 3 rows
minimum. If my column has less than 3 rows, it's not picking up that
particular column of data into the new single column. Is there a way to fix
the macro?

Thanks

Raj

"Gord Dibben" wrote:

Raj

Note that thewre are two versions of the macro at that google post.

Copy the second version which Bernie modified then.........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + R to open Project Explorer.

Find your workbook/project and select it.

Right-click and InsertModule. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run the macro by going to ToolMacroMacros.

You can also assign this macro to a button or a shortcut key combo.


Gord

On Tue, 15 Aug 2006 12:26:02 -0700, Raj wrote:

Hi again Gord

I tried copy pasting the macro to the ToolsMacroVB
editorSheet1InsertModule. Am I doing anything wrong? My skills are quite
basic with this? Would appreciate if you could explain a little more.

Thanks

Raj

"Raj" wrote:

Thanks Gord... will try it out tonight and will let you know the outcome...
thanks again

Raj

"Raj" wrote:

Hi there everyone

I have a table of data 10 rows and 10 columns wide with some blanks in
between. Is there a way to format it to a single column of data (order
doesn't matter). The output essentially just needs to be one column of data,
1 cell wide. If the blanks can be eliminated, nothing like it... if not, even
then I can live with that!!

Thanks guys,

Appreciate.

Raj.

Gord Dibben MS Excel MVP


Gord Dibben MS Excel MVP

Raj

The macro is designed/written for uneven columns, not uneven rows.

Try running this WrapUnder macro first to get data into 5 columns(blanks
included but ignore them for now).

Then run OneColumnV2 macro and be sure to answer "Yes" to "Exclude Blanks"

Sub WrapUnder()
Dim i As Long
For i = Range("A1").End(xlDown).Row To Range("A1").Row Step -1
Cells(i + 1, 1).EntireRow.Insert
Cells(i + 1, 1).EntireRow.Insert
Cells(i, 1).Range("f1:J1").Cut Cells(i + 1, 1)
Cells(i, 1).Range("K1:O1").Cut Cells(i + 2, 1)
Next i
End Sub


Gord


On Thu, 17 Aug 2006 08:22:02 -0700, Raj wrote:

Hi Gord

Sorry, for the delay in responding. I understand what you say. If you have a
table in excel where the first row of data has the least number of columns
than the bottom ones, the macro is not picking up all the data. Something
like the following:
a b c d e
f g h i j k l m
n o p q r s t u v w x y z

In this case the macro is picking up only 15 of the above as the first row
has only 5.

Hope I could explain.

Thanks
Raj

"Gord Dibben" wrote:

I cannot replicate your problem even when I have a column with only one entry in
row 1 and nothing else.

Answer is: I don't know what to fix


Gord

On Tue, 15 Aug 2006 14:06:02 -0700, Raj wrote:

Oh! Hi Gord... yes it's working to the point that my column has upto 3 rows
minimum. If my column has less than 3 rows, it's not picking up that
particular column of data into the new single column. Is there a way to fix
the macro?

Thanks

Raj

"Gord Dibben" wrote:

Raj

Note that thewre are two versions of the macro at that google post.

Copy the second version which Bernie modified then.........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + R to open Project Explorer.

Find your workbook/project and select it.

Right-click and InsertModule. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run the macro by going to ToolMacroMacros.

You can also assign this macro to a button or a shortcut key combo.


Gord

On Tue, 15 Aug 2006 12:26:02 -0700, Raj wrote:

Hi again Gord

I tried copy pasting the macro to the ToolsMacroVB
editorSheet1InsertModule. Am I doing anything wrong? My skills are quite
basic with this? Would appreciate if you could explain a little more.

Thanks

Raj

"Raj" wrote:

Thanks Gord... will try it out tonight and will let you know the outcome...
thanks again

Raj

"Raj" wrote:

Hi there everyone

I have a table of data 10 rows and 10 columns wide with some blanks in
between. Is there a way to format it to a single column of data (order
doesn't matter). The output essentially just needs to be one column of data,
1 cell wide. If the blanks can be eliminated, nothing like it... if not, even
then I can live with that!!

Thanks guys,

Appreciate.

Raj.

Gord Dibben MS Excel MVP


Gord Dibben MS Excel MVP

Thanks Gord... your macro is doing the groundwork perfectly... thanks very
much.

Raj

"Gord Dibben" wrote:

Raj

The macro is designed/written for uneven columns, not uneven rows.

Try running this WrapUnder macro first to get data into 5 columns(blanks
included but ignore them for now).

Then run OneColumnV2 macro and be sure to answer "Yes" to "Exclude Blanks"

Sub WrapUnder()
Dim i As Long
For i = Range("A1").End(xlDown).Row To Range("A1").Row Step -1
Cells(i + 1, 1).EntireRow.Insert
Cells(i + 1, 1).EntireRow.Insert
Cells(i, 1).Range("f1:J1").Cut Cells(i + 1, 1)
Cells(i, 1).Range("K1:O1").Cut Cells(i + 2, 1)
Next i
End Sub


Gord


On Thu, 17 Aug 2006 08:22:02 -0700, Raj wrote:

Hi Gord

Sorry, for the delay in responding. I understand what you say. If you have a
table in excel where the first row of data has the least number of columns
than the bottom ones, the macro is not picking up all the data. Something
like the following:
a b c d e
f g h i j k l m
n o p q r s t u v w x y z

In this case the macro is picking up only 15 of the above as the first row
has only 5.

Hope I could explain.

Thanks
Raj

"Gord Dibben" wrote:

I cannot replicate your problem even when I have a column with only one entry in
row 1 and nothing else.

Answer is: I don't know what to fix


Gord

On Tue, 15 Aug 2006 14:06:02 -0700, Raj wrote:

Oh! Hi Gord... yes it's working to the point that my column has upto 3 rows
minimum. If my column has less than 3 rows, it's not picking up that
particular column of data into the new single column. Is there a way to fix
the macro?

Thanks

Raj

"Gord Dibben" wrote:

Raj

Note that thewre are two versions of the macro at that google post.

Copy the second version which Bernie modified then.........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + R to open Project Explorer.

Find your workbook/project and select it.

Right-click and InsertModule. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run the macro by going to ToolMacroMacros.

You can also assign this macro to a button or a shortcut key combo.


Gord

On Tue, 15 Aug 2006 12:26:02 -0700, Raj wrote:

Hi again Gord

I tried copy pasting the macro to the ToolsMacroVB
editorSheet1InsertModule. Am I doing anything wrong? My skills are quite
basic with this? Would appreciate if you could explain a little more.

Thanks

Raj

"Raj" wrote:

Thanks Gord... will try it out tonight and will let you know the outcome...
thanks again

Raj

"Raj" wrote:

Hi there everyone

I have a table of data 10 rows and 10 columns wide with some blanks in
between. Is there a way to format it to a single column of data (order
doesn't matter). The output essentially just needs to be one column of data,
1 cell wide. If the blanks can be eliminated, nothing like it... if not, even
then I can live with that!!

Thanks guys,

Appreciate.

Raj.

Gord Dibben MS Excel MVP


Gord Dibben MS Excel MVP

Took a while for me to get the full picture of your layout<g

Gord

On Thu, 17 Aug 2006 12:37:13 -0700, Raj wrote:

Thanks Gord... your macro is doing the groundwork perfectly... thanks very
much.

Raj

"Gord Dibben" wrote:

Raj

The macro is designed/written for uneven columns, not uneven rows.

Try running this WrapUnder macro first to get data into 5 columns(blanks
included but ignore them for now).

Then run OneColumnV2 macro and be sure to answer "Yes" to "Exclude Blanks"

Sub WrapUnder()
Dim i As Long
For i = Range("A1").End(xlDown).Row To Range("A1").Row Step -1
Cells(i + 1, 1).EntireRow.Insert
Cells(i + 1, 1).EntireRow.Insert
Cells(i, 1).Range("f1:J1").Cut Cells(i + 1, 1)
Cells(i, 1).Range("K1:O1").Cut Cells(i + 2, 1)
Next i
End Sub


Gord


On Thu, 17 Aug 2006 08:22:02 -0700, Raj wrote:

Hi Gord

Sorry, for the delay in responding. I understand what you say. If you have a
table in excel where the first row of data has the least number of columns
than the bottom ones, the macro is not picking up all the data. Something
like the following:
a b c d e
f g h i j k l m
n o p q r s t u v w x y z

In this case the macro is picking up only 15 of the above as the first row
has only 5.

Hope I could explain.

Thanks
Raj

"Gord Dibben" wrote:

I cannot replicate your problem even when I have a column with only one entry in
row 1 and nothing else.

Answer is: I don't know what to fix


Gord

On Tue, 15 Aug 2006 14:06:02 -0700, Raj wrote:

Oh! Hi Gord... yes it's working to the point that my column has upto 3 rows
minimum. If my column has less than 3 rows, it's not picking up that
particular column of data into the new single column. Is there a way to fix
the macro?

Thanks

Raj

"Gord Dibben" wrote:

Raj

Note that thewre are two versions of the macro at that google post.

Copy the second version which Bernie modified then.........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + R to open Project Explorer.

Find your workbook/project and select it.

Right-click and InsertModule. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run the macro by going to ToolMacroMacros.

You can also assign this macro to a button or a shortcut key combo.


Gord

On Tue, 15 Aug 2006 12:26:02 -0700, Raj wrote:

Hi again Gord

I tried copy pasting the macro to the ToolsMacroVB
editorSheet1InsertModule. Am I doing anything wrong? My skills are quite
basic with this? Would appreciate if you could explain a little more.

Thanks

Raj

"Raj" wrote:

Thanks Gord... will try it out tonight and will let you know the outcome...
thanks again

Raj

"Raj" wrote:

Hi there everyone

I have a table of data 10 rows and 10 columns wide with some blanks in
between. Is there a way to format it to a single column of data (order
doesn't matter). The output essentially just needs to be one column of data,
1 cell wide. If the blanks can be eliminated, nothing like it... if not, even
then I can live with that!!

Thanks guys,

Appreciate.

Raj.

Gord Dibben MS Excel MVP


Gord Dibben MS Excel MVP




Gord Dibben MS Excel MVP