Forum Home
Press F1
 
Thread ID: 86665 2008-01-24 10:22:00 Excel formula bpt2 (6653) Press F1
Post ID Timestamp Content User
633545 2008-01-24 10:22:00 How do I count the number of same values (any values) in a row that appear more than once; e.g. if the number "3" appears 7 times or 5 appears 3 times, I want a formula that produces those results. bpt2 (6653)
633546 2008-01-24 11:32:00 COUNTIF will do the job perfectly. Just highlight the applicable range of cells and put in the figure that you want counted.

If you wanted to look multiple figures up, you could also use COUNTIFS. 		beeswax34 (63)
633547 2008-01-24 12:28:00 Yes, but what if I want to simply check if there are any numbers which appear more than once? bpt2 (6653)
633548 2008-01-24 20:42:00 Here's a quick example done in Excel 2007 that should get you started:

i120.photobucket.com

HTH 		LCA (11357)
633549 2008-01-24 21:37:00 bpt2:

You need to be clearer in what you want. A formula will only output a single result in the cell it is entered into. So if you want to know how many 3's there are you can write a function to do this using the countif function like LCA noted. So you could have a table with the numbers ranging from the highest to the lowest values in your list and put countif functions beside them to count the number of each type. If you just want to know if there are any duplicates with a yes/no result then you can write a custom function.

NB you need to press ctrl+shift+enter to enter an array formula from memory, I always have to muck around to get them to work. 		pico (4752)
633550 2008-01-25 11:18:00 It is the latter I'm after. How do I write that? bpt2 (6653)
633551 2008-01-25 19:23:00 Hi

If you are wanting a custom function to return either true or false then the following function added to a standard VBA module will do the trick :


Public Function FindDupes(InputRange As Range) As Boolean

Dim Cell As Range, OuterLoop As Long, InnerLoop As Long, PosCount As Long
Dim MyArray() As String

ReDim MyArray(InputRange.Cells.Count)

FindDupes = False
PosCount = 0
For Each Cell In InputRange
PosCount = PosCount + 1
MyArray(PosCount) = Cell.Value
Next

For OuterLoop = 1 To PosCount - 1
For InnerLoop = OuterLoop + 1 To PosCount
If MyArray(OuterLoop) = MyArray(InnerLoop) Then
FindDupes = True
Exit Function
End If
Next InnerLoop
Next OuterLoop

End Function

To open the VBA screen while in Excel press the Alt and F11 keys. To add a standard module select menu option 'Insert' > 'Module'. Copy > paste the code into the screen on the right (it should be called 'Module 1' per the list on the left). To call this function from your worksheet, enter the following formula :

=FindDupes(A1:A100) {or whatever range you want to search}

Alternatively, the following array formula will also return a true/false value without the need for any VBA code :


=IF(SUM(COUNTIF(A2:A10,"="&A2:A10))>COUNT(A2:A10),TRUE)

Change the range from A2:A10 to whatever range you want. This is an array formula so after entering the formula, press the Ctrl + Shift + Enter keys (and not just the enter key). You will know if you have done this correctly because curly brackets will appear at each end of the formula.


HTH, Andrew 		andrew93 (249)
633552 2008-01-26 04:31:00 Thanks for that ..................where do you learn all this stuff??? bpt2 (6653)
633553 2008-01-26 04:36:00 Thanks for that ..................where do you learn all this stuff???

www.mrexcel.com
:) 		andrew93 (249)
633554 2008-01-27 20:47:00 bpt2: sorry I didn't answer your question for you but glad someone could help you out.

andrew93: Did you write the vb function or copy it from the website posted? The way I was going to approach it was to get the list, sort it into numerical order then loop through each entry and compare it with the las one to see if it was the same and if so exit and return true otherwise false. The only dumb thing is that vb doesn't have a list object like in .net that has a sort method. You have to write your own method (as far as I know). By the way I like your method anyway. 		pico (4752)
1 2