Convert output from vertical to horizontal please.

S.H.A.D.O.

S.H.A.D.O.

Well-known Member
Joined
Sep 6, 2005
Messages
1,915
Good morning,

I have the following code which lists the output vertically, but I would like it to list the output horizontally please.
The first figure is the total Odd numbers for position 1, the second figure is the total Even numbers for position 1, and the third figure is the total Odd & Even numbers for position 1, and so on down for positions 2 to 6.

The information is output like so...

[TABLE="width: 48"]
<tbody>[TR]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]16044
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]11088
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]27132
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]13188
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]13944
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]27132
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]13608
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]13524
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]27132
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]13608
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]13524
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]27132
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]13188
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]13944
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]27132
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]16044
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]11088
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]27132
[/TD]
[/TR]
</tbody>[/TABLE]

...but I would like it like so...

[TABLE="width: 288"]
<tbody>[TR]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]16044
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]13188
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]13608
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]13608
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]13188
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]16044
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]11088
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]13944
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]13524
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]13524
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]13944
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]11088
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]27132
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]27132
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]27132
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]27132
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]27132
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]27132
[/TD]
[/TR]
</tbody>[/TABLE]

Here is the code...

Code: 
Option Explicit
Option Base 1
Const MinA As Integer = 1
Const MaxF As Integer = 59
Sub Odd_And_Even_By_Position()
    Dim A As Long, B As Long, C As Long, D As Long, E As Long, F As Long
    Dim nDist(1 To 18) As Double
    Dim n As Integer
    Dim i As Integer
    With Application
        .ScreenUpdating = False: .Calculation = xlCalculationManual: .DisplayAlerts = False
    End With
    Columns("A:C").ClearContents
    Cells(1, 1).Select
    For A = MinA To MaxF - 5
        For B = A + 1 To MaxF - 4
            For C = B + 1 To MaxF - 3
                For D = C + 1 To MaxF - 2
                    For E = D + 1 To MaxF - 1
                        For F = E + 1 To MaxF
                            nDist(2 - (A Mod 2)) = nDist(2 - (A Mod 2)) + 1
                            nDist(5 - (B Mod 2)) = nDist(5 - (B Mod 2)) + 1
                            nDist(8 - (C Mod 2)) = nDist(8 - (C Mod 2)) + 1
                            nDist(11 - (D Mod 2)) = nDist(11 - (D Mod 2)) + 1
                            nDist(14 - (E Mod 2)) = nDist(14 - (E Mod 2)) + 1
                            nDist(17 - (F Mod 2)) = nDist(17 - (F Mod 2)) + 1
                        Next F
                    Next E
                Next D
            Next C
        Next B
    Next A
    For i = 1 To 6
        nDist(i * 3) = nDist(i * 3 - 1) + nDist(i * 3 - 2)
    Next i
    For n = LBound(nDist) To UBound(nDist)
        ActiveCell.Offset(n - LBound(nDist), 1) = nDist(n)
    Next n
    With Application
        .DisplayAlerts = True: .Calculation = xlCalculationAutomatic: .ScreenUpdating = True
    End With
End Sub

Any help will be appreciated, thanks in advance.
 
Last edited:

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Sort by date Sort by votes
Try this

Replace
Code: 
 For n = LBound(nDist) To UBound(nDist)
        ActiveCell.Offset(n - LBound(nDist), 1) = nDist(n)
 Next n


With

Code: 
MyRow = ActiveCell.Row+1
 MyCol = 0
 For n = LBound(nDist) To UBound(nDist)
        MyCol = MyCol + 1
        ActiveCell.Offset(MyRow, MyCol) = nDist(n)
        If MyCol = 6 Then
          MyCol = 0
          MyRow = MyRow + 1
        End If
 Next n
 
Last edited:
Upvote 0
Thanks for the reply CharlesChuckieCharles, unfortunately it is not giving the output in the correct format.
Is there anyway to do this by changing the structure of...

Code: 
                            nDist(2 - (A Mod 2)) = nDist(2 - (A Mod 2)) + 1
                            nDist(5 - (B Mod 2)) = nDist(5 - (B Mod 2)) + 1
                            nDist(8 - (C Mod 2)) = nDist(8 - (C Mod 2)) + 1
                            nDist(11 - (D Mod 2)) = nDist(11 - (D Mod 2)) + 1
                            nDist(14 - (E Mod 2)) = nDist(14 - (E Mod 2)) + 1
                            nDist(17 - (F Mod 2)) = nDist(17 - (F Mod 2)) + 1

...to produce...


<colgroup><col style="width:48pt" span="6" width="64"> </colgroup><tbody>
[TD="class: xl65, width: 64, align: right"]16044[/TD]
[TD="class: xl65, width: 64, align: right"]13188[/TD]
[TD="class: xl65, width: 64, align: right"]13608[/TD]
[TD="class: xl65, width: 64, align: right"]13608[/TD]
[TD="class: xl65, width: 64, align: right"]13188[/TD]
[TD="class: xl65, width: 64, align: right"]16044[/TD]

[TD="class: xl65, align: right"]11088[/TD]
[TD="class: xl65, align: right"]13944[/TD]
[TD="class: xl65, align: right"]13524[/TD]
[TD="class: xl65, align: right"]13524[/TD]
[TD="class: xl65, align: right"]13944[/TD]
[TD="class: xl65, align: right"]11088[/TD]

[TD="class: xl65, align: right"]27132[/TD]
[TD="class: xl65, align: right"]27132[/TD]
[TD="class: xl65, align: right"]27132[/TD]
[TD="class: xl65, align: right"]27132[/TD]
[TD="class: xl65, align: right"]27132[/TD]
[TD="class: xl65, align: right"]27132[/TD]

</tbody>

Thanks in advance.
 
Last edited:
Upvote 0
Try this For Data in column "A" and results starting "F1"
Code: 
[COLOR="Navy"]Sub[/COLOR] MG17Jun19
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, C [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
C = 0
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] C Mod 3 = 0 [COLOR="Navy"]Then[/COLOR]
        Ac = Ac + 1: C = 1
    [COLOR="Navy"]Else[/COLOR]
        C = C + 1
    [COLOR="Navy"]End[/COLOR] If
    Cells(C, Ac + 3) = Dn.Value
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Last edited:
Upvote 0
Sorry wrong way round

MyRow = ActiveCell.Row
MyCol = 1
For n = LBound(nDist) To UBound(nDist)
Myrow=Myrow+1
ActiveCell.Offset(MyRow, MyCol) = nDist(n)
If Myrow-Activecell.row = 3 Then
MyCol = MyCol+1
MyRow = 0
End If
Next n
 
Upvote 0
Thanks Mick for the reply. A nice way of taking the outputed data and reorganising it.

Thanks CharlesChuckieCharles,

I changed the line MyRow = 0 to MyRow = 1 and it worked perfectly, thanks.
I was trying to amend the code within the 6 For...Next loops to try and get rid of the numbers and minus before the Mod which I thought would make it a lot simpler.

Thanks again, and I hope you have a great weekend.
 
Upvote 0
MickG said:
Try this For Data in column "A" and results starting "F1"
Code: 
[COLOR=Navy]Sub[/COLOR] MG17Jun19
[COLOR=Navy]Dim[/COLOR] Rng [COLOR=Navy]As[/COLOR] Range, Dn [COLOR=Navy]As[/COLOR] Range, C [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] Ac [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]
[COLOR=Navy]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
C = 0
[COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] Dn [COLOR=Navy]In[/COLOR] Rng
    [COLOR=Navy]If[/COLOR] C Mod 3 = 0 [COLOR=Navy]Then[/COLOR]
        Ac = Ac + 1: C = 1
    [COLOR=Navy]Else[/COLOR]
        C = C + 1
    [COLOR=Navy]End[/COLOR] If
    Cells(C, Ac + 3) = Dn.Value
[COLOR=Navy]Next[/COLOR] Dn
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]
Regards Mick
Click to expand...


Mick, You taught me to write cell values all at once for better performance but now you're going towards old way :)

SHADOW, MOD is costly Operator. Here is another approach for better speed if you've large number of elements in array.

Code: 
Sub reorganize()
Dim nar() As Variant

ReDim nar(1 To 3, 1 To UBound(ndist) / 3)

For j = LBound(ndist) To UBound(ndist) Step 3
        k = k + 1
        For i = 1 To 3
            nar(i, k) = ndist(j + i - 1)
        Next
Next
Range("C1").Resize(3, UBound(nar, 2)) = nar
End Sub

Regards,
Ombir
 
Last edited:
Upvote 0
Ombir said:
Mick, You taught me to write cell values all at once for better performance but now you're going towards old way :)

SHADOW, MOD is costly Operator. Here is another approach for better speed if you've large number of elements in array.

Code: 
Sub reorganize()
Dim[B][COLOR="#FF0000"][SIZE=2] i As Long, j As Long, k As Long, ndist As Variant,[/SIZE][/COLOR][/B]  nar As Variant

[B][COLOR="#FF0000"][SIZE=2]ndist = Range("A1", Cells(Rows.Count, "A").End(xlUp))[/SIZE][/COLOR][/B]

ReDim nar(1 To 3, 1 To UBound(ndist) / 3)

For j = LBound(ndist) To UBound(ndist) Step 3
        k = k + 1
        For i = 1 To 3
            nar(i, k) = ndist(j + i - 1[B][COLOR="#FF0000"][SIZE=2], 1[/SIZE][/COLOR][/B])
        Next
Next
Range("C1").Resize(3, UBound(nar, 2)) = nar
End Sub
Click to expand...
You will need to add what I show in red above in order to make your code work. Note that I removed the empty set of parentheses from the declaration for the nar variable as they are not needed.
 
Last edited:
Upvote 0
Thanks to everyone who has contributed to this, it is appreciated.

Wouldn't it be easier to change the original code I posted to do this, as that is the format I would like, I don't want the output to be vertical, but horizontal. I just want the output from the code to be...

[TABLE="width: 654"]
<tbody>[TR]
[TD="class: xl63, width: 145, bgcolor: transparent, align: right"]16044
[/TD]
[TD="class: xl63, width: 145, bgcolor: transparent, align: right"]13188
[/TD]
[TD="class: xl63, width: 145, bgcolor: transparent, align: right"]13608
[/TD]
[TD="class: xl63, width: 145, bgcolor: transparent, align: right"]13608
[/TD]
[TD="class: xl63, width: 145, bgcolor: transparent, align: right"]13188
[/TD]
[TD="class: xl63, width: 145, bgcolor: transparent, align: right"]16044
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]11088
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]13944
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]13524
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]13524
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]13944
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]11088
[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"]27132
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]27132
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]27132
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]27132
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]27132
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]27132
[/TD]
[/TR]
</tbody>[/TABLE]

...starting in B1.

It is in the For...Next loops that I can't seem to adapt as I assume that this is what is needed.

Thanks in advance.
 
Upvote 0
Hi Rick,

I was only focusing on another approach using For loop but thanks anyway for correction.

Regards,
Ombir
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question Question
create fixtures but 2 vs 2
Replies
7
Views
151
kerm007
K
P
  • Solved
Lambda to count pairs and output array based on criteria
2
Replies
19
Views
498
Phantom1
P
M
  • Question Question
Macro to change data from vertically to horizontally
Replies
4
Views
152
Mussa
M
J
  • Question Question
Combining VBA code together...need help!
Replies
3
Views
378
Fluff
Fluff
J
  • Question Question
Automatically move cells from one sheet to another based on criteria using VBA
Replies
5
Views
709
DanteAmor
DanteAmor

Forum statistics

Threads
1,223,276
Messages
6,171,140
Members
452,381
Latest member
Nova88

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top