Math Help Needed

[Icewynd]

In my Pick 3 analysis, I break up the 10 digits in various ways to get a handle on trends. Two of these methods use a 3-4-3 split: Cold-Warm-Hot and Low-Middle-High.

The most common distribution of drawn digits over these categories is 0-1-2 (0-2-1, 1-2-0, 1-0-2, 2-1-0, 2-0-1) e.g. 0 Cold, 1 Warm, 2 Hot. Currently the '0-1-2' category represents 58% of draws for Low-Middle-High andd 76% of draws for the Cold-Warm-Hot distribution. One of these is too high or too low.

How can I calculate the expected proportion of 0-1-2 draws? Can anyone help with this?

 

[PAB]

Hi Icewynd,

How can I calculate the expected proportion of 0-1-2 draws?

To calculate this use the formula...

=COMBIN(3,0)*COMBIN(3,1)*COMBIN(3,2)

... which will return 9.
To calculate the percentage use this formula...

=COMBIN(3,0)*COMBIN(3,1)*COMBIN(3,2)/COMBIN(10,3)%

... which will return 7.50%

I hope this helps!

Regards,
PAB


-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
Mathematics is the language of nature.
Everything around us can be represented and understood through numbers.
If you graph the numbers of any system, patterns emerge. Therefore, there are patterns everywhere in nature.

 

[Icewynd]

Thanks, PAB, but that's not quite it, since the numbers are drawn with replacement. However, your reply set me on the right road (I believe).

There are 3 possibilities: 1 number from each group (1-1-1), 3 numbers from one group and zero from the others (3-0-0, 0-3-0, 0-0-3) and the 6 combinations of 0-1-2.

For 1-1-1, we take one from the first group of 3; one from the middle group of 4 and one from the last group of 3. This gives us 3*4*3=36, multiplied by 6 for the possible different orders =216 combinations out of 1000, or 21.6%.

For 0-0-3, we have one possible unmatched combination for each of the 2 groups of 3 and 4 unmatched combinations from the group of 4, giving us ((1*2)+4)*6=36 unmatched combinations. Further, we have 6 doubles from the groups of 3 and 12 doubles from the group of 4 giving us ((6*2)+(12))*3=72 double combinations. Additionally, we have 3 triple combinations from the groups of 3 plus 4 triple combinations from the group of 4 for a total of 10 triple combinations. In total 0-0-3 gives us 36+72+10=118 combinations out of 1000, or 11.8%

So, my expected distribution would be:

1-1-1 21.6%
0-0-3 11.8%
0-1-2 66.6%

Correct?? Or did I take a wrong turn somewhere?

Also, I suspect that there is a more elegant and less time-consuming way to figure this out. Does anyone have the formula?

 

[PAB]

Hi Icewynd,

I live in the UK so I obviously do not know how this particular Lotto is formatted or configured.
If as you say, the numbers are drawn with replacement, could you not use something like this...

=PERMUT(10,0)*PERMUT(10,1)*PERMUT(10,2)

... or am I totally missing the point here?

Regards,
PAB


-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
Mathematics is the language of nature.
Everything around us can be represented and understood through numbers.
If you graph the numbers of any system, patterns emerge. Therefore, there are patterns everywhere in nature.

 

[Icewynd]

The game is called Pick 3, where you pick a 3 digit combination out of 10 digits (0-9). The combinations can consist of all different digits (e.g. 123), 2 different digits (eg. 559) or 1 digit (eg. 888). There are 720 unmatched combinations, 210 double combinations and 10 triple combinations.

My question is specific to a certain method that I use to analyse the digits in play by dividing them into low (0,1,2), middle (3-6) and high (7-9). The most common distribution of digits across these groups is 0 from one group, 1 from another group and 2 from the 3rd group (6 possible ways this can happen). My answer above that this happens 66.6% of the time 'feels right' given my historical results, but it you can tell me how to confirm this without counting combinations, I would appreciate it.

 

[PAB]

Hi Icewynd,

I am just on my way out but I thought I would put this code together for you.
It will give you EVERY combination of the Pick3.

Option Explicit
Option Base 1

Option Explicit
Option Base 1

Sub List_Permutations_Pick_3()
Dim A As Integer
Dim B As Integer
Dim C As Integer
Dim nMinA As Long
Dim nMaxF As Long
Dim n As Long
With Application
.ScreenUpdating = False: .Calculation = xlCalculationManual: .DisplayAlerts = False
End With
nMinA = 0
nMaxF = 9
With Sheets("Sheet1").Select
Cells.EntireColumn.Delete
Range("A1").Select
n = 0
For A = nMinA To nMaxF
For B = nMinA To nMaxF
For C = nMinA To nMaxF
ActiveCell.Offset(n, 0).Value = A
ActiveCell.Offset(n, 1).Value = B
ActiveCell.Offset(n, 2).Value = C
ActiveCell.Offset(1, 0).Select
Next C
Next B
Next A
n = n + 1
End With
Cells.EntireColumn.AutoFit: Cells.EntireColumn.HorizontalAlignment = xlCenter
With Application
.DisplayAlerts = True: .Calculation = xlCalculationAutomatic: .ScreenUpdating = True
End With
End Sub

Let me know how you get on!

Regards,
PAB


-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
Mathematics is the language of nature.
Everything around us can be represented and understood through numbers.
If you graph the numbers of any system, patterns emerge. Therefore, there are patterns everywhere in nature.

 

[Frank]

Hi icewynd,

I too had problems getting my head around your lottery and your system of grouping. Correct me if i'm wrong, but if you classify the digits into one of three groups low, middle and high and do this over the three columns of digits, would you not end up with 27 possible classifications ? (I presume you mean 1=low, 2=middle and 3=high)

low low low or (1-1-1)
low low middle
low low high

low middle low
low middle middle
low middle high

low high low
low high middle
low high high


middle low low
middle low middle
middle low high

middle middle low
middle middle middle
middle middle high


middle high low
middle high middle
middle high high

high low low
high low middle
high low high

high middle low
high middle middle
high middle high

high high low
high high middle
high high high


It is possible to count up how many (low low lows) for example there can be (26) and put values to the other combinations of high and low.
I suspect you want to know about specific combinations to suit your 0-1-2 array ( 3 lots of 6 =18) from the list in any order (one group missing from each three )

such as:-

low low high...... low high low........ high low low

low low middle......... low middle low...... middle low low

middle middle low...... low middle middle...... middle low middle

middle middle high...... high middle middle...... middle high middle

high high low ...... low high high...... high low high

high high middle...... middle high high....... high middle high

How many altogether as a percentage of the 27 totals from the first list ?
Am I getting warm ?
If so, I don't fancy writing a macro (PAB might) but it can be done with formulas and autofilter to trap each group, and then an hour or so to work through the totals manually filtering each digit column.

 

[Icewynd]

Wow, thanks PAB. That is sure to come in handy. I envy your ability to just whip off a macro like that!

Frank,

There are 10 different variations on these categories:

1-1-1 (1 of 3 low digits, 1 of 4 middle digits and one of 3 high digits, for example 159)

0-0-3 (3 of low, middle or high, with no digits from the other two groups, for example 012, 334, 888)
003, 030, 300 = 3

0-1-2 (2 from one group, 1 from another, no digits from the third group, e.g. 378, 015, 189)
012, 021, 102, 120, 210, 201 =6

So I would like to know what proportion of the total is represented by any of the variations of 0-1-2.

Both your inputs are much appreciated!

 

[Frank]

Well I'm obviously on a different wavelength so l
I'll have to declare myself out. Sorry.

 

[PAB]

Frank,

You're input is always more than welcome, I have had a blind spot on this probably because I have done 36 hours stretch and didn't fully understand the criteria involved.
I will try and add the criteria that Icewynd wants into the program and then try and formulate the according Excel formulas.
I think that this is brcause we are using PERMUTATIONS as opposed to COMBINATIONS.

Regards,
PAB


-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
Mathematics is the language of nature.
Everything around us can be represented and understood through numbers.
If you graph the numbers of any system, patterns emerge. Therefore, there are patterns everywhere in nature.

 

[PAB]

Hi Icewynd,

I have put this code together that gives ALL the distributions, the total number of combinations for each distribution, the percentage and the expected to be drawn 1 in "n" draws.

L = LOW ( Numbers - 0,1,2 )
M = MIDDLE ( Numbers - 3,4,5,6 )
H = HIGH ( Numbers - 7,8,9 )

Option Explicit
Option Base 1

Sub List_Permutations_Pick_3_PAB()
Dim A As Integer
Dim B As Integer
Dim C As Integer
Dim nMinA As Long
Dim nMaxF As Long
Dim i As Integer
Dim n As Long
Dim Dist As Double
Dim nDist(27) As Double
Dim TotalComb As Long
With Application
.ScreenUpdating = False: .Calculation = xlCalculationManual: .DisplayAlerts = False
End With
nMinA = 0
nMaxF = 9
TotalComb = 1000
For i = 1 To 9
nDist(i) = 0
Next i
With Sheets("Sheet1").Select
Cells.EntireColumn.Delete
Range("A1").Select
n = 0
For A = nMinA To nMaxF
For B = nMinA To nMaxF
For C = nMinA To nMaxF
If A <= 2 And B <= 2 And C <= 2 Then nDist(1) = nDist(1) + 1
If A <= 2 And B <= 2 And C >= 3 And C <= 6 Then nDist(2) = nDist(2) + 1
If A <= 2 And B <= 2 And C >= 7 Then nDist(3) = nDist(3) + 1
If A <= 2 And B >= 3 And B <= 6 And C <= 2 Then nDist(4) = nDist(4) + 1
If A <= 2 And B >= 3 And B <= 6 And C >= 3 And C <= 6 Then nDist(5) = nDist(5) + 1
If A <= 2 And B >= 3 And B <= 6 And C >= 7 Then nDist(6) = nDist(6) + 1
If A <= 2 And B >= 7 And C <= 2 Then nDist(7) = nDist(7) + 1
If A <= 2 And B >= 7 And C >= 3 And C <= 6 Then nDist(8) = nDist(8) + 1
If A <= 2 And B >= 7 And C >= 7 Then nDist(9) = nDist(9) + 1
If A >= 3 And A <= 6 And B <= 2 And C <= 2 Then nDist(10) = nDist(10) + 1
If A >= 3 And A <= 6 And B <= 2 And C >= 3 And C <= 6 Then nDist(11) = nDist(11) + 1
If A >= 3 And A <= 6 And B <= 2 And C >= 7 Then nDist(12) = nDist(12) + 1
If A >= 3 And A <= 6 And B >= 3 And B <= 6 And C <= 2 Then nDist(13) = nDist(13) + 1
If A >= 3 And A <= 6 And B >= 3 And B <= 6 And C >= 3 And C <= 6 Then nDist(14) = nDist(14) + 1
If A >= 3 And A <= 6 And B >= 3 And B <= 6 And C >= 7 Then nDist(15) = nDist(15) + 1
If A >= 3 And A <= 6 And B >= 7 And C <= 2 Then nDist(16) = nDist(16) + 1
If A >= 3 And A <= 6 And B >= 7 And C >= 3 And C <= 6 Then nDist(17) = nDist(17) + 1
If A >= 3 And A <= 6 And B >= 7 And C >= 7 Then nDist(18) = nDist(18) + 1
If A >= 7 And B <= 2 And C <= 2 Then nDist(19) = nDist(19) + 1
If A >= 7 And B <= 2 And C >= 3 And C <= 6 Then nDist(20) = nDist(20) + 1
If A >= 7 And B <= 2 And C >= 7 Then nDist(21) = nDist(21) + 1
If A >= 7 And B >= 3 And B <= 6 And C <= 2 Then nDist(22) = nDist(22) + 1
If A >= 7 And B >= 3 And B <= 6 And C >= 3 And C <= 6 Then nDist(23) = nDist(23) + 1
If A >= 7 And B >= 3 And B <= 6 And C >= 7 Then nDist(24) = nDist(24) + 1
If A >= 7 And B >= 7 And C <= 2 Then nDist(25) = nDist(25) + 1
If A >= 7 And B >= 7 And C >= 3 And C <= 6 Then nDist(26) = nDist(26) + 1
If A >= 7 And B >= 7 And C >= 7 Then nDist(27) = nDist(27) + 1
Next C
Next B
Next A
n = n + 1
Range("A1").Value = "Dist"
Range("A2").Value = "LLL"
Range("A3").Value = "LLM"
Range("A4").Value = "LLH"
Range("A5").Value = "LML"
Range("A6").Value = "LMM"
Range("A7").Value = "LMH"
Range("A8").Value = "LHL"
Range("A9").Value = "LHM"
Range("A10").Value = "LHH"
Range("A11").Value = "MLL"
Range("A12").Value = "MLM"
Range("A13").Value = "MLH"
Range("A14").Value = "MML"
Range("A15").Value = "MMM"
Range("A16").Value = "MMH"
Range("A17").Value = "MHL"
Range("A18").Value = "MHM"
Range("A19").Value = "MHH"
Range("A20").Value = "HLL"
Range("A21").Value = "HLM"
Range("A22").Value = "HLH"
Range("A23").Value = "HML"
Range("A24").Value = "HMM"
Range("A25").Value = "HMH"
Range("A26").Value = "HHL"
Range("A27").Value = "HHM"
Range("A28").Value = "HHH"
Range("B1").Value = "Comb"
Range("C1").Value = "Percent"
Range("D1").Value = "Expected 1 in"
For i = 1 To 27
With ActiveCell
.Offset(i, 1).Value = nDist(i)
.Offset(i, 2).Value = 100 / TotalComb * nDist(i)
.Offset(i, 3).Value = TotalComb / nDist(i)
.Offset(i, 4).Value = "Draws"
.Offset(i + 1, 1).FormulaR1C1 = "=Sum(R2C2:R[-1]C)"
.Offset(i + 1, 2).FormulaR1C1 = "=Sum(R2C3:R[-1]C)"
.Offset(i, 1).NumberFormat = "#,##0"
.Offset(i, 2).NumberFormat = "##0.00"
.Offset(i, 3).NumberFormat = "##0.0000"
End With
Next i
End With
Cells.EntireColumn.AutoFit: Cells.EntireColumn.HorizontalAlignment = xlRight
With Application
.DisplayAlerts = True: .Calculation = xlCalculationAutomatic: .ScreenUpdating = True
End With
End Sub

Let me know what you think!

Regards,
PAB


-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
Mathematics is the language of nature.
Everything around us can be represented and understood through numbers.
If you graph the numbers of any system, patterns emerge. Therefore, there are patterns everywhere in nature.

 

[PAB]

Duplicate post deleted.

Regards,
PAB


-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
Mathematics is the language of nature.
Everything around us can be represented and understood through numbers.
If you graph the numbers of any system, patterns emerge. Therefore, there are patterns everywhere in nature.

 

[Icewynd]

Thanks, PAB. That works perfectly!!!

Your macro confirms my estimation above, but it is also very helpful to know the number of each type of combination.

Again, a big THANKS for all your hard work!

 

[PAB]

Hi Icewynd,

You're welcome .
I have modified the code slightly to show the number of different distributions in the first column.

Option Explicit
Option Base 1

Sub List_Permutations_Pick_3_PAB()
Dim A As Integer
Dim B As Integer
Dim C As Integer
Dim nMinA As Long
Dim nMaxF As Long
Dim i As Integer
Dim n As Long
Dim Dist As Double
Dim nDist(27) As Double
Dim TotalComb As Long
With Application
.ScreenUpdating = False: .Calculation = xlCalculationManual: .DisplayAlerts = False
End With
nMinA = 0
nMaxF = 9
TotalComb = 1000
For i = 1 To 9
nDist(i) = 0
Next i
With Sheets("Sheet1").Select
Cells.EntireColumn.Delete
Range("A1").Select
n = 0
For A = nMinA To nMaxF
For B = nMinA To nMaxF
For C = nMinA To nMaxF
If A <= 2 And B <= 2 And C <= 2 Then nDist(1) = nDist(1) + 1
If A <= 2 And B <= 2 And C >= 3 And C <= 6 Then nDist(2) = nDist(2) + 1
If A <= 2 And B <= 2 And C >= 7 Then nDist(3) = nDist(3) + 1
If A <= 2 And B >= 3 And B <= 6 And C <= 2 Then nDist(4) = nDist(4) + 1
If A <= 2 And B >= 3 And B <= 6 And C >= 3 And C <= 6 Then nDist(5) = nDist(5) + 1
If A <= 2 And B >= 3 And B <= 6 And C >= 7 Then nDist(6) = nDist(6) + 1
If A <= 2 And B >= 7 And C <= 2 Then nDist(7) = nDist(7) + 1
If A <= 2 And B >= 7 And C >= 3 And C <= 6 Then nDist(8) = nDist(8) + 1
If A <= 2 And B >= 7 And C >= 7 Then nDist(9) = nDist(9) + 1
If A >= 3 And A <= 6 And B <= 2 And C <= 2 Then nDist(10) = nDist(10) + 1
If A >= 3 And A <= 6 And B <= 2 And C >= 3 And C <= 6 Then nDist(11) = nDist(11) + 1
If A >= 3 And A <= 6 And B <= 2 And C >= 7 Then nDist(12) = nDist(12) + 1
If A >= 3 And A <= 6 And B >= 3 And B <= 6 And C <= 2 Then nDist(13) = nDist(13) + 1
If A >= 3 And A <= 6 And B >= 3 And B <= 6 And C >= 3 And C <= 6 Then nDist(14) = nDist(14) + 1
If A >= 3 And A <= 6 And B >= 3 And B <= 6 And C >= 7 Then nDist(15) = nDist(15) + 1
If A >= 3 And A <= 6 And B >= 7 And C <= 2 Then nDist(16) = nDist(16) + 1
If A >= 3 And A <= 6 And B >= 7 And C >= 3 And C <= 6 Then nDist(17) = nDist(17) + 1
If A >= 3 And A <= 6 And B >= 7 And C >= 7 Then nDist(18) = nDist(18) + 1
If A >= 7 And B <= 2 And C <= 2 Then nDist(19) = nDist(19) + 1
If A >= 7 And B <= 2 And C >= 3 And C <= 6 Then nDist(20) = nDist(20) + 1
If A >= 7 And B <= 2 And C >= 7 Then nDist(21) = nDist(21) + 1
If A >= 7 And B >= 3 And B <= 6 And C <= 2 Then nDist(22) = nDist(22) + 1
If A >= 7 And B >= 3 And B <= 6 And C >= 3 And C <= 6 Then nDist(23) = nDist(23) + 1
If A >= 7 And B >= 3 And B <= 6 And C >= 7 Then nDist(24) = nDist(24) + 1
If A >= 7 And B >= 7 And C <= 2 Then nDist(25) = nDist(25) + 1
If A >= 7 And B >= 7 And C >= 3 And C <= 6 Then nDist(26) = nDist(26) + 1
If A >= 7 And B >= 7 And C >= 7 Then nDist(27) = nDist(27) + 1
Next C
Next B
Next A
n = n + 1
Range("A1").Value = "No"
Range("B1").Value = "Dist"
Range("C1").Value = "Comb"
Range("D1").Value = "Percent"
Range("E1").Value = "Expected 1 in"
Range("B2").Value = "LLL"
Range("B3").Value = "LLM"
Range("B4").Value = "LLH"
Range("B5").Value = "LML"
Range("B6").Value = "LMM"
Range("B7").Value = "LMH"
Range("B8").Value = "LHL"
Range("B9").Value = "LHM"
Range("B10").Value = "LHH"
Range("B11").Value = "MLL"
Range("B12").Value = "MLM"
Range("B13").Value = "MLH"
Range("B14").Value = "MML"
Range("B15").Value = "MMM"
Range("B16").Value = "MMH"
Range("B17").Value = "MHL"
Range("B18").Value = "MHM"
Range("B19").Value = "MHH"
Range("B20").Value = "HLL"
Range("B21").Value = "HLM"
Range("B22").Value = "HLH"
Range("B23").Value = "HML"
Range("B24").Value = "HMM"
Range("B25").Value = "HMH"
Range("B26").Value = "HHL"
Range("B27").Value = "HHM"
Range("B28").Value = "HHH"
For i = 1 To 27
With ActiveCell
.Offset(i, 0).Value = (i)
.Offset(i, 2).Value = nDist(i)
.Offset(i, 3).Value = 100 / TotalComb * nDist(i)
.Offset(i, 4).Value = TotalComb / nDist(i)
.Offset(i, 5).Value = "Draws"
.Offset(i + 1, 2).FormulaR1C1 = "=Sum(R2C3:R[-1]C)"
.Offset(i + 1, 3).FormulaR1C1 = "=Sum(R2C4:R[-1]C)"
.Offset(i, 2).NumberFormat = "#,##0"
.Offset(i, 3).NumberFormat = "##0.00"
.Offset(i, 4).NumberFormat = "##0.0000"
End With
Next i
End With
Cells.EntireColumn.AutoFit: Cells.EntireColumn.HorizontalAlignment = xlRight
With Application
.DisplayAlerts = True: .Calculation = xlCalculationAutomatic: .ScreenUpdating = True
End With
End Sub

Have FUN and good luck!

Regards,
PAB


-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
Mathematics is the language of nature.
Everything around us can be represented and understood through numbers.
If you graph the numbers of any system, patterns emerge. Therefore, there are patterns everywhere in nature.

 

[Icewynd]

Super! Thanks so much.

 

[PAB]

Hi Icewynd,

Thinking about your specific method that you use to analyse the digits in play by dividing them into LOW (0,1,2), MIDDLE (3,4,5,6) and HIGH (7,8,9) I decided to update what I have previously posted with another Macro to allow you to input either L, M or H in cells E2, F2 and G2 to get ALL the 3 digit combinations that are relevent for that criteria.
I have setup a table with the criteria in that if you so wish, you can change the parameters of the table from what you have currently specified.

Input these into Sheet1:

Cell E1 enter the word Criteria
Cells E2:G2 enter either L, M or H
Cell E3 enter the word VLOOKUP
Cell E4 enter H
Cell E5 enter L
Cell E6 enter M
Cell F4 enter >=7
Cell F5 enter <=2
Cell F6 enter >=3
Cell G4 enter >=7
Cell G5 enter <=2
Cell G6 enter <=6

Obviously ALL the combinations are in columns A:C as per the previous Macro.
Copy and paste this Macro into a Module and attach it to a Button that you put in cell E8.

Sub List_Permutations_Pick_3_Set_Criteria_PAB()
Dim FormulaStr As String
Dim rRng As Range
Set rRng = Range("I1:I1000")
With Application
.ScreenUpdating = False: .Calculation = xlCalculationManual: .DisplayAlerts = False
End With
With Sheets("Sheet1")
Columns("I:I").ClearContents
FormulaStr = "=IF(AND(A1" & Application.VLookup(.Range("E2"), .Range("E4:G12"), 2, 0) & _
",A1" & Application.VLookup(.Range("E2"), .Range("E4:G12"), 3, 0) & _
",B1" & Application.VLookup(.Range("F2"), .Range("E4:G12"), 2, 0) & _
",B1" & Application.VLookup(.Range("F2"), .Range("E4:G12"), 3, 0) & _
",C1" & Application.VLookup(.Range("G2"), .Range("E4:G12"), 2, 0) & _
",C1" & Application.VLookup(.Range("G2"), .Range("E4:G12"), 3, 0) & "),A1&B1&C1,"""")"
Range("I1").Formula = FormulaStr
Application.Calculation = xlCalculationAutomatic
Range("I1").Copy Range("I2", Range("A1").End(xlDown).Offset(, 8))
Application.Calculation = xlCalculationManual
With Sheets("Sheet1")
rRng.Copy: rRng.PasteSpecial xlPasteValues
.Sort.SortFields.Clear
.Sort.SortFields.Add Key:=rRng, SortOn:=xlSortOnValues, _
Order:=xlAscending, DataOption:=xlSortTextAsNumbers
With Sheets("Sheet1").Sort
.SetRange rRng
.Apply
End With
Range("E11").Select
End With
End With
With Application
.DisplayAlerts = True: .Calculation = xlCalculationAutomatic: .ScreenUpdating = True
End With
End Sub

Have FUN!
Let me know what you think please!

Regards,
PAB


-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
Mathematics is the language of nature.
Everything around us can be represented and understood through numbers.
If you graph the numbers of any system, patterns emerge. Therefore, there are patterns everywhere in nature.

 

[Icewynd]

Hi PAB,

This is really above and beyond! What a fantasic idea.

Unfortunately, this isn't working for me. When I hit the button it is doing something, (blue circle appears), but it doesn't produce any output. Any ideas?

 

[PAB]

Hi Icewynd,

Have you got the 1,000 combinations in columns A:C in the same sheet?
Have you put the table in the correct cells with the correct parameters?

Regards,
PAB


-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
Mathematics is the language of nature.
Everything around us can be represented and understood through numbers.
If you graph the numbers of any system, patterns emerge. Therefore, there are patterns everywhere in nature.

 

[PAB]

Hi Icewynd,

Did you manage to sort it out?

Regards,
PAB


-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
Mathematics is the language of nature.
Everything around us can be represented and understood through numbers.
If you graph the numbers of any system, patterns emerge. Therefore, there are patterns everywhere in nature.

 

[Icewynd]

Hi PAB,

Sorry, we had to go out last night, so I couldn't deal with it then. But, I had missed the step of copying all the combinations into columns A:C.

Runs like a dream now!