still need help! removing doubles

[caroline.vanbommel]

In A1 i have the number 1489 in B1 i have the number 12489 and in C1 i have 35789.

I want in D1 to show me the values that occur in all 3 or more of the cells. thus the result would be 89. the numbers are allways in the order 123456789. so 8 is never followed by 3 or 5.

No its not a sudoku but anwers to a questionaire i want to see the most repetitive number from a list of numeric answers

 

[lozzablake]

this user defined function will do what you want. Note it returns a string

Function TripletCheck(intOne As Long, intTwo As Long, intThree As Long)
'determines if any single number contained within intOne also appears in intTwo and intThree


    Dim i As Integer
  
    'loop through all the numbers within intOne checking to see if in int Two/Three
    For i = 1 To Len(intOne)
        If InStr(1, intTwo, Mid(intOne, i, 1)) > 0 And InStr(1, intThree, Mid(intOne, i, 1)) > 0 Then
            TripletCheck = TripletCheck & Mid(intOne, i, 1)
        End If
    Next i
    
End Function

 

[caroline.vanbommel]

is there no formula that can do this for me. i dont mind adding columns or something like that. im a noob at VB so this code says nothing to me! hehe

 

[patrickmuldoon99]

Caroline,

lozzablake has provided a function for you that can be used as a formula.

Press Alt+F11 when you are in Excel. This will bring up the VBE window. Goto Insert --> Module and paste the code lozzablake has provided in there.

Then, in your worksheet, you can use the formula:

= TripletCheck(A1,B1,C1)

Hope this helps!

Patrick