Welcome Guest
You last visited December 7, 2016, 1:12 pm
All times shown are
Eastern Time (GMT-5:00)

# Excel Experts needed

Topic closed. 3 replies. Last post 5 years ago by phileight.

 Page 1 of 1
Member #19992
August 9, 2005
2867 Posts
Offline
 Posted: July 14, 2011, 6:41 pm - IP Logged
Can anyone create for me a tool that can generate numbers from distinct pairs from 2 groups of pairs without it generating numbers from all combined pairs?

I will give you an example of what I want and don't want...

09
04
24
16
27
38
13
57
18
25
47
58

23
56
67
06
26
46
05
17
35
59
14
69
68
12
34
78
19

In the above pairs we start with a 09 pair group...... I ONLY want the 09 pair to combine with the pairs that have an "0" and a "9" in them in the 2nd group of pairs.... so 09 matches with all 0 numbers (06,05) to make sets of 095 and 096 and matches the 9 from 09 with the other pairs (59,69,19) to make sets 095, 096 091
THEY DO NOT HAVE TO BE IN SAME ORDER... 9 can be either front or back same with 0

I DON'T want it to match up pairs within the first pairs group or 2nd pairs group with 2nd group... like 05 and 06 to get 056

Is this possible to do? I would be ever so greatful if anyone can build a spreadsheet for me like this...thanks in advance. You have brilliant minds and I need someone like that to figure this out...lol
If not can someone tell me a formula that one could use to do this type of  thing.

If possible can 6 ways be implimented as an option as well.... maybe to the side of original panel... not sure if this is possible lol... thanks

We are all Lucky... just some of us don't realize it!

United States
Member #41846
June 23, 2006
459 Posts
Offline
 Posted: July 14, 2011, 8:50 pm - IP Logged
Can anyone create for me a tool that can generate numbers from distinct pairs from 2 groups of pairs without it generating numbers from all combined pairs?

I will give you an example of what I want and don't want...

09
04
24
16
27
38
13
57
18
25
47
58

23
56
67
06
26
46
05
17
35
59
14
69
68
12
34
78
19

In the above pairs we start with a 09 pair group...... I ONLY want the 09 pair to combine with the pairs that have an "0" and a "9" in them in the 2nd group of pairs.... so 09 matches with all 0 numbers (06,05) to make sets of 095 and 096 and matches the 9 from 09 with the other pairs (59,69,19) to make sets 095, 096 091
THEY DO NOT HAVE TO BE IN SAME ORDER... 9 can be either front or back same with 0

I DON'T want it to match up pairs within the first pairs group or 2nd pairs group with 2nd group... like 05 and 06 to get 056

Is this possible to do? I would be ever so greatful if anyone can build a spreadsheet for me like this...thanks in advance. You have brilliant minds and I need someone like that to figure this out...lol
If not can someone tell me a formula that one could use to do this type of  thing.

If possible can 6 ways be implimented as an option as well.... maybe to the side of original panel... not sure if this is possible lol... thanks

questions  the wording says 0 "and" a 9  but example would indicate "or". I'll assume example is correct

where are your pairs?  all in col A  or 1 set in A other set in B?  are the # of pairs always the same or vary?

what col do you want the results to be in

if your data base is in same workbook you could generate a list of matching box hits and game # for each combination. would need to know how your file is structured.

Member #19992
August 9, 2005
2867 Posts
Offline
 Posted: July 14, 2011, 9:19 pm - IP Logged

questions  the wording says 0 "and" a 9  but example would indicate "or". I'll assume example is correct

where are your pairs?  all in col A  or 1 set in A other set in B?  are the # of pairs always the same or vary?

what col do you want the results to be in

if your data base is in same workbook you could generate a list of matching box hits and game # for each combination. would need to know how your file is structured.

I sent you a file.. i just entered them in a space I found...lol... but if you can figure it out by all means go for it...lol

We are all Lucky... just some of us don't realize it!

United States
Member #41846
June 23, 2006
459 Posts
Offline
 Posted: July 16, 2011, 2:37 pm - IP Logged

Raksters file has 1st set of pairs in cells R8 through R19. second set of pairs is in cells R20 through R36.

he had empty space after line 41 so I put the results in col R starting in line 42.  you will need to duplicate these conditions or modify this code to suit your conditions.  let me know if I can help. p8

Sub Make_comb()
' take pairs from sheet 1  yellow is 1 set white is second set  split into digits make comb if 1 digit of pair
'yellow matches 1 digit in pair white
' pair data first group start line 8 col R or (18) thru line 19
'pair data 2nd group line 20 col R thru line 36
Dim l(999) ' gives me a place to store comb created and count them at same time
Range("T42:U585").Select ' clear any previous results
Selection.ClearContents
'outside loop first pairs
For x = 8 To 19 '  this is all of the group 1 pairs
pr1 = Cells(x, 18) '  take value in row 8 col R which is also 18 and easier to work with store in variable pr1
n1 = Left\$(pr1, 1): n2 = Right\$(pr1, 1) ' seperates the digits if pr1=09 then n1=0  n2=9
'start inner loop or 2nd pairs group
For y = 20 To 36 ' all of group 2 pairs
pr2 = Cells(y, 18)
n3 = Left\$(pr2, 1): n4 = Right\$(pr2, 1) ' I now have the individual digits for both pairs time to compare
If n1 = n3 Then n5 = n4: GoSub create_comb
If n1 = n4 Then n5 = n3: GoSub create_comb
If n2 = n3 Then n5 = n4: GoSub create_comb
If n2 = n4 Then n5 = n3: GoSub create_comb
Next y 'loop through 2nd pairs
Next x ' loop thru ist pairs
' display results starting row 42 col T or (20)
v = 42
For z = 0 To 999
If l(z) > 0 Then Cells(v, 20) = z: Cells(v, 21) = l(z): v = v + 1
Next z

Range("U42").Select
Selection.Sort Key1:=Range("U42"), Order1:=xlDescending

Exit Sub

create_comb: '1 digit in pair 1 matched 1 or more digits in pair 2
' check for doubles
'If n1 = n2 Or n1 = n5 Or n2 = n5 Then GoTo dblquit

m1 = n1: m2 = n2: m5 = n5
For z = 1 To 3: If m1 < m2 Then m6 = m2: m2 = m1: m1 = m6
If m2 < m5 Then m6 = m5: m5 = m2: m2 = m6
Next z ' sort the 3 digits hi to low
a = m1 * 100 + m2 * 10 + m5 ' convert digits to number 0-999
l(a) = l(a) + 1 '  keep track of comb created and count how many times it occurs
dblquit:
Return

End Sub

 Page 1 of 1