Convert output from vertical to horizontal please.

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:
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.
This will be faster than the approach you tried to use (which will be especially noticeable if you have a lot of data), it is the code modification (of Ombir's code) that I posted in Message #8 but modified to start its output at cell B1...
Code:
Sub reorganize()
  
  Dim i As Long, j As Long, k As Long, ndist As Variant, nar As Variant
  
  ndist = Range("A1", Cells(Rows.Count, "A").End(xlUp))
  
  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, 1)
    Next
  Next
  
  Range("B1").Resize(3, UBound(nar, 2)) = nar

End Sub
 
Upvote 0

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Hi Rick, thanks for the updated code.

The updated code though only works once my original code has run and the data has been outputted to the WorkSheet.
I would like the original code to run and output the format horizontally. This way there is no need to run any additional code to reorganise unwanted data.

Basically, the original code outputs the data vertically and I would like it output horizontally instead.

I hope this makes more sense?

Thanks again.
 
Upvote 0
Hi, s.h.a.d.o.
what abiut changing this line

Code:
ActiveCell.Offset(n - LBound(nDist), 1) = nDist(n)

to this

Code:
ActiveCell.Offset(IIf(n Mod 3 = 0, 3, n Mod 3), Int(n / 3) - IIf(n Mod 3 = 0, 1, 0)) = nDist(n)

hope this helps
 
Upvote 0
Thanks B___P, that did the trick.

Is there anyway that I could start the output in B2 as opposed to A2 please?

My original thinking was to amend/change the 6 For...Next loops with respect to the numbers followed by a minus before the Mod, which I thought would make it a lot simpler. I must have tried every which way but without success.
 
Upvote 0
How are you with x-files, still wanting to believe?

I read your posts and guess you are doing a lot of descriptive statistics, any idea about forecasting or inferential stats?

Remaining on films I prefer "back to the future" where the scientist developed a total theory. I am thinking about confidence intervals on delay frequencies. It looks powerful but may be better I go out to buy NEXT week newspaper to read results...:laugh:
 
Last edited:
Upvote 0
Yes, I must admit that I am a great X-Files fan.

I bought the complete Box-Set a few years ago, then watched them all in sequence, yes, that's right, ALL 208 episodes.
Although you can turn on pretty much any episode and understand it because they have complete stories within them from start to finish, watching them in sequence gives you the added hidden stories entwined throughout the whole 10 series, mainly Mulders search for what happened to his sister.

The whole series is on my agenda to watch again this year, at some point.

I know they recently did a 6 episode series 10, which I did find a little disappointing, but it is rumoured that a series 11 might very well be in the pipeline, well let's hope so anyway.

As far as the lottery is concerned, every combination has as much chance as any other combination of being drawn.
The only real way to increase your chances of winning is to buy more tickets.
That said, I like numerology and number manipulation, so over the last 22 years of the UK lottery I have tried to come up with data and scenarios that might give me that little bit of an edge. Needless to say, I am still trying. OH well, there is always tonights draw, although I have been saying that for 22 years.

Have a great weekend.
 
Upvote 0
In my country lotto is the oldest game. The statistics look back to 1871 and at present time there are 3 drawns a week. The numbers are from 1 to 90 and 5 are drawn. I was writing about inference because in the first part of last century an engineer developed a formula to tell how many times a certain number can lack in sequentially drawns. This number is called delay and is funtion of propability and total numbers already drawn. I tried to adapt it to more complex combinations and results are consistent with real data. I need more to get rid of an unfair game but is a starting point.

Let's wait this evening....
 
Last edited:
Upvote 0
I was writing about inference because in the first part of last century an engineer developed a formula to tell how many times a certain number can lack in sequntially drawns. This number is called delay and is funtion of propability and total numbers already drawn. I tried to adapt it to more complex combinations and results are consistent with real data.
Here in the UK we have 2 main draws a week where 6 numbers are drawn from a pool of 59 numbers and then an additional ball (bonus ball) is drawn from the same pool of numbers.

Your comment above on inference/delay sounds quite interesting, you don't happen to know what the engineers name was do you?

I must admit that I use the lottery as a way of getting used to coding, most times without success I might add.
 
Upvote 0
The name was Samaritani. He published his work in italian.
Coding for lottery is a way to test my skills: I have an idea about possible results and can evaluate on the fly the precision of my algorithms.
BTW Samaritani's work is not accepted by many mathemathicians and is not clear either it is a maximum or a mode. Opponents often explain their reasons with superficiality as they were prisoner of theorems. The common is: numbers have no memory. My favourite answer is "If they have no memory the risk is they forget statistics too".
I found the work something to think about and not heretical. Doing inference we think about media and variance to evaluate a fork around media. It's hard to find something about the extreme tails of a distribution...
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,277
Messages
6,171,150
Members
452,383
Latest member
woodsfordg

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