Converting a number of rows into a single column

[Guest]

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]

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

http://snipurl.com/uxol


Gord Dibben MS Excel MVP

 

[Guest]

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

Raj

 

[Guest]

Hi again Gord

I tried copy pasting the macro to the Tools>Macro>VB
editor>Sheet1>Insert>Module. Am I doing anything wrong? My skills are quite
basic with this? Would appreciate if you could explain a little more.

Thanks

Raj

 

[Gord Dibben]

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 Insert>Module. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run the macro by going to Tool>Macro>Macros.

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


Gord

Hi again Gord

I tried copy pasting the macro to the Tools>Macro>VB
editor>Sheet1>Insert>Module. Am I doing anything wrong? My skills are quite
basic with this? Would appreciate if you could explain a little more.

Thanks

Raj

Gord Dibben MS Excel MVP

 

[Guest]

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]

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

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 MS Excel MVP

 

[Guest]

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]

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

 

[Guest]

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

Raj

 

[Gord Dibben]

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

Gord

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

Raj

Gord Dibben MS Excel MVP