Welcome Guest
Log In | Register )
You last visited December 10, 2016, 7:11 pm
All times shown are
Eastern Time (GMT-5:00)

Need Excell Help Again

Topic closed. 2 replies. Last post 5 years ago by Sage.

Page 1 of 1
PrintE-mailLink
Sage's avatar - Lottery-035.jpg
Dunwoody Georgia
United States
Member #747
September 29, 2002
659 Posts
Offline
Posted: September 24, 2011, 12:29 am - IP Logged

Does anyone know the formula to search a cash 4 number for doubles.  Say I have 1443 I want to be able to use a macro to search the 1443 and pull out the double 44's?  Or if it can be done wihtout a macro that's fine also.  Thanks so much in advance.  Smile

It's Never Too Late To Be The Person You Could Of Been!I Agree!

    Avatar

    United States
    Member #41846
    June 23, 2006
    460 Posts
    Offline
    Posted: September 24, 2011, 5:18 am - IP Logged

    Sage  this macro will show you doubles in a data set. I put in a lot of comments so hopefully you can follow what the code does and modify to suit your situation.

     

    Sub show_doubles()
    ' assumptions
    ' data is in col a-d
    'show results in col f, and g if more than 1
    'clear output area in case there is something there
    Range("f1:g7000").Select
    Selection.ClearContents
    Dim tn(10) ' work space, for me tn means test number
    ' start a loop
    For x = 1 To 10000 ' if you have more than 10000 change this #
    a = Cells(x, 1): If a = "" Then GoTo done 'if out of data then your done

    For z = 0 To 9: tn(z) = 0: Next z ' clear the array for each loop

    For y = 1 To 4
    a = Cells(x, y): tn(a) = tn(a) + 1 'get data from the 4 col. count# times a 4 shows
    Next y

    ' this loop will look for a value of 2 in the tn array display if found
    cl = 6 ' or col "F" reset each loop
    For Db = 0 To 9
    If tn(Db) = 2 Then Cells(x, cl) = Db: cl = cl + 1

    ' if tn(db)=2 then cells(x,5)="D"   'if you only want to see a D then use this line instead
    Next Db

    Next x ' completes outer loop

    done:

    End Sub

      Sage's avatar - Lottery-035.jpg
      Dunwoody Georgia
      United States
      Member #747
      September 29, 2002
      659 Posts
      Offline
      Posted: September 24, 2011, 8:28 am - IP Logged

      Sage  this macro will show you doubles in a data set. I put in a lot of comments so hopefully you can follow what the code does and modify to suit your situation.

       

      Sub show_doubles()
      ' assumptions
      ' data is in col a-d
      'show results in col f, and g if more than 1
      'clear output area in case there is something there
      Range("f1:g7000").Select
      Selection.ClearContents
      Dim tn(10) ' work space, for me tn means test number
      ' start a loop
      For x = 1 To 10000 ' if you have more than 10000 change this #
      a = Cells(x, 1): If a = "" Then GoTo done 'if out of data then your done

      For z = 0 To 9: tn(z) = 0: Next z ' clear the array for each loop

      For y = 1 To 4
      a = Cells(x, y): tn(a) = tn(a) + 1 'get data from the 4 col. count# times a 4 shows
      Next y

      ' this loop will look for a value of 2 in the tn array display if found
      cl = 6 ' or col "F" reset each loop
      For Db = 0 To 9
      If tn(Db) = 2 Then Cells(x, cl) = Db: cl = cl + 1

      ' if tn(db)=2 then cells(x,5)="D"   'if you only want to see a D then use this line instead
      Next Db

      Next x ' completes outer loop

      done:

      End Sub

      Thank you so much Iwill give this a try and let you know how I do!  Thanks again and have a wonderful weekend!  Thumbs UpWinkParty

      It's Never Too Late To Be The Person You Could Of Been!I Agree!