Single column to multiple columns

[pag.reynolds1986]

Hi There,

I was wondering if anyone could help.

I have a a colleague who has a small database on excel 2010, there are 3 columns of data, which are too large to print out on one page, however scaling it down is not possible as this would make the printout too small to read.. This has meant that in the past he has stacked the columns to the right hand side of each other, rather like:


a
b
c
d
e
f
g
h
i
j


going to

a d g j
b e h
c f i

to fit onto an A4 sheet. The problem comes when he has to insert a new 'letter' in the sequence, it takes a lot of time due to the insertion of an entire row and then manipulating the dataset again so that it all is in the right order.

Is there are way to have a multiple column continuation of the same column?Or is there a way to have all the data in a single column each and then display the data in stacked columns?

Any suggestions - even to call me a excel fool would be appreciated.

 

[lhkittle]

Hi There,



I was wondering if anyone could help.



I have a a colleague who has a small database on excel 2010, there are 3 columns of data, which are too large to print out on one page, however scaling it down is not possible as this would make the printout too small to read. This has meant that in the past he has stacked the columns to the righthand side of each other, rather like:





a

b

c

d

e

f

g

h

i

j





going to



a d g j

b e h

c f i



to fit onto an A4 sheet. The problem comes when he has to insert a new 'letter' in the sequence, it takes a lot of time due to the insertion of an entire row and then manipulating the dataset again so that it all is in the right order.



Is there are way to have a multiple column continuation of the same column? Or is there a way to have all the data in a single column each and then display the data in stacked columns?



Any suggestions - even to call me a excel fool would be appreciated.

Well, this may be a start. Probably will need some tweeking.
Takes columns A, B, C and puts them in cells A1,A2,A3.

If you need each value in a seperate cell then some text to columns code could do that.

Sub SuperJoin()

Range("D1") = Join(Application.Transpose(Range(Range("A1"), _
Range("A" & Rows.Count).End(xlUp))), " ") '" / ")

Range("D2") = Join(Application.Transpose(Range(Range("B1"), _
Range("B" & Rows.Count).End(xlUp))), " ") '" / ")

Range("D3") = Join(Application.Transpose(Range(Range("C1"), _
Range("C" & Rows.Count).End(xlUp))), " ") '" / ")

Range("A1:C" & Range("C" & Rows.Count).End(xlUp).Row).ClearContents

Range("D13").Cut Range("A1")

End Sub

Regards,
Howard

 

[Claus Busch]

Hi,

Am Wed, 10 Apr 2013 08:20:10 -0700 (PDT) schrieb
(e-mail address removed):

a
b
c
d
e
f
g
h
i
j

going to

a d g j
b e h
c f i

try:
Sub PrintMultipleColumn()
Dim wsh1 As Worksheet
Dim wsh2 As Worksheet
Dim i As Long
Dim j As Long
Dim col As Long
Dim LRow As Long

'Modify sheet name
Set wsh1 = Worksheets("myData")
Set wsh2 = Worksheets.Add
LRow = wsh1.Cells(Rows.Count, 1).End(xlUp).Row
i = 1
j = 1
col = 1
With wsh1
Do While i <= LRow
For col = 1 To 6 Step 3
.Range(wsh1.Cells(i, 1), .Cells(i + 49, 3)).Copy
wsh2.Paste Destination:=wsh2.Range(wsh2.Cells(j, col), _
wsh2.Cells(j + 49, col + 1))
i = i + 50
Next
j = j + 50
wsh2.Rows(j).PageBreak = xlManual
Loop
End With
wsh2.PrintPreview
'wsh2.PrintOut
'Application.DisplayAlerts = False
'wsh2.Delete
'Application.DisplayAlerts = True
End Sub


Regards
Claus Busch

 

[Claus Busch]

Hi again,

Am Wed, 10 Apr 2013 18:10:08 +0200 schrieb Claus Busch:

For col = 1 To 6 Step 3

if you want one blank column between the data, then change line above
to:
For col = 1 To 6 Step 4


Regards
Claus Busch

 

[lhkittle]

Well, this may be a start. Probably will need some tweeking.

Takes columns A, B, C and puts them in cells A1,A2,A3.



If you need each value in a seperate cell then some text to columns code could do that.



Sub SuperJoin()



Range("D1") = Join(Application.Transpose(Range(Range("A1"), _

Range("A" & Rows.Count).End(xlUp))), " ") '" / ")



Range("D2") = Join(Application.Transpose(Range(Range("B1"), _

Range("B" & Rows.Count).End(xlUp))), " ") '" / ")



Range("D3") = Join(Application.Transpose(Range(Range("C1"), _

Range("C" & Rows.Count).End(xlUp))), " ") '" / ")



Range("A1:C" & Range("C" & Rows.Count).End(xlUp).Row).ClearContents



Range("D13").Cut Range("A1")



End Sub



Regards,

Howard

I totally missread the question... Now that I see Claus's response.

H'wd

 

[Puppet_Sock]

Hi There,

I was wondering if anyone could help.

I have a a colleague who has a small database on excel 2010, there are 3 columns of data, which are too large to print out on one page, however scaling it down is not possible as this would make the printout too small to read. This has meant that in the past he has stacked the columns to the righthand side of each other, rather like:

a
b
c
d
e
f
g
h
i
j

going to

a d g j
b e h
c f i

to fit onto an A4 sheet. The problem comes when he has to insert a new 'letter' in the sequence, it takes a lot of time due to the insertion of an entire row and then manipulating the dataset again so that it all is in the right order.

Is there are way to have a multiple column continuation of the same column? Or is there a way to have all the data in a single column each and then display the data in stacked columns?

Any suggestions - even to call me a excel fool would be appreciated.

I would just write a simple little VBA script. It would be about 20
lines.