Welcome Guest
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
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.

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

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

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!

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

 Page 1 of 1