Welcome Guest
Log In | Register )
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
PrintE-mailLink
Rakster's avatar - praying hands.jpg
Saskatchewan
Canada
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!

    Avatar

    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.

      Rakster's avatar - praying hands.jpg
      Saskatchewan
      Canada
      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!

        Avatar

        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