Excel Covert Number Table to Column 2

[mlevine]

Hello, after searching the threads on Tables and Row manipulation, I need to ask.

Looking to see if there is a way to convert Table information (numbers) into a column format.

Example:
From:
1 2 3 4 (each number in its own cell)
5 6 7 8
9 0

To:

1
2
3
4
5
6
7
8
9

I can do this on a per row basis,(Copy paste Special) but the table is a bit long to do this to over 100 lines...

Any Macros or thoughts?

Thank you

Matt

 

[tdcjchief]

Try this
1.Highlight/select the entire table or cells containing the data.
2. Go to Table and select split cells, change the number of colums to 1 and the rows to 1.
3. click okay and it should work.

 

[KenWright]

Assuming your data is as per your example (4 columns wide)in say A1100, then in say cell H1 put the following formula and copy down to H400:-

=OFFSET($A$1,FLOOR((ROW()-5)/4,1),MOD(ROW()-5,4))

You'll probably get some #NUM errors in the first few rows but just ignore those, and you will see your data series start probably in row 5.

When done, simply copy column H and paste special as values and you can then delete the source data if you wish.

Regards
Ken..........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[klmorgan]

Or Copy/ Paste Special/ Tick Transpose box

Regards

Keith

 

[klmorgan]

Sorry

Just read your post to the end!!!
Keith

 

[mlevine]

Hello All, Will give these a try once I get to work this Morn.

Just a small note Ken, the columns are a fixed number wide but more than 4, I assume just increase that variable if I give your formula a try.

I will let you all know..

Thanks!

 

[ETID]

A VBA solution....

Sub Grid_to_Column()
Dim C As Range, r As Integer, sheet_name As String

Columns("A:A").Select
Selection.Insert Shift:=xlToRight
sheet_name = ActiveSheet.Name
r = 1

For Each C In Sheets(sheet_name).UsedRange.Cells
If C.Value <> "" Then
Cells(r, 1) = C.Value
r = r + 1
Else
End If
Next C

End Sub

 

[KenWright]

Where n is number of columns:-

=OFFSET($A$1,FLOOR((ROW()-(n+1))/n,1),MOD(ROW()-(n+1),n))

Also just a minor correction but I should have said copy down to H404 given that the first few rows would return #NUM, but you can do the checks to make sure you get all the data.

Regards
Ken..........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[mlevine]

KenWright,
Used your updated one and works nice. I will try to use that VBA ETID but, will probably get the local programmer to watch over that one.

Thanks Guys!

 

[KenWright]

You're welcome

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[AnotherHiggins]

mlevine,

I see you've been a member for a while. Please consider awarding one of this little purple stars to KenWright and/or ETID for taking the time to help you.

It's as easy as pressing the little "Thank _____ for this valuable post!" link at the bottom of every post in a thread.

Doing this is not only a way of saying 'Thanks', it also lets future visitors see that this thread was successfully concluded.

[tt]_____
[blue]-John[/blue]
[/tt][red]Quidquid latine dictum sit, altum viditur[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[AnotherHiggins]

PS - Please do NOT give me a star for this thread.

[tt]_____
[blue]-John[/blue]
[/tt][red]Quidquid latine dictum sit, altum viditur[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[mlevine]

My apologies, I never got into the mechanics of the site... I thought stars were based on number of posts one may answer/repond too.. hence automatically.

Ken and Etid.. you have been given your respective "Stars".

ML

Illius me paenitet, dux