still need help! removing doubles

caroline.vanbommel

New Member
Joined
Aug 21, 2006
Messages
27
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
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

lozzablake

Well-known Member
Joined
Dec 15, 2005
Messages
818
this user defined function will do what you want. Note it returns a string

Code:
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

New Member
Joined
Aug 21, 2006
Messages
27
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

Active Member
Joined
Jun 27, 2006
Messages
345
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
 

Forum statistics

Threads
1,136,366
Messages
5,675,354
Members
419,564
Latest member
Phil57

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top