Another option that you may wish to consider is a user-defined function.
Advantage:
- Simpler formula on the worksheet itself
- Easily expand to more columns if required
Disadvantage:
- User must have macros enabled
To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula(s) as shown in the screen shot below.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)
Rich (BB code):
Function CountDupes(r As Range, Optional bExcludeFirst As Boolean, Optional bMatchCase As Boolean) As Long
Dim a As Variant, e As Variant
Dim d As Object
Dim i As Long
Dim s As String
a = r.Value
Set d = CreateObject("Scripting.dictionary")
If Not bMatchCase Then d.comparemode = 1
For i = 1 To UBound(a)
s = Join(Application.Index(a, i, 0), "|")
If Len(s) >= UBound(a, 2) Then d(s) = d(s) + 1
Next i
For Each e In d.Items
If e > 1 Then CountDupes = CountDupes + e - IIf(bExcludeFirst, 1, 0)
Next e
End Function
Examples of use below.
This function has an added option as to whether to count items as equal if their case (upper/lower) is identical or not - see cell E3
For the two option arguments in the function, you can use True/False or 1/0. If omitted, those arguments will be treated as False)
If more columns are to be included, just change the range in the function on the worksheet. eg for 10 columns =CountDupes(A1:J9)
Excel 2010 32 bit
| A | B | C | D | E |
|---|
| 1 | 1 | aaa | x | | 5 |
| 2 | 1 | aaa | x | | 3 |
| 3 | | | | | 4 |
| 4 | 4 | e | | | |
| 5 | 2 | r | | | |
| 6 | | | | | |
| 7 | z | z | | | |
| 8 | 2 | r | | | |
| 9 | 1 | AAA | x | | |
<colgroup><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Count Dupes
Worksheet Formulas
| Cell | Formula |
|---|
| E1 | =CountDupes(A1:C9) |
|---|
| E2 | =CountDupes(A1:C9,TRUE) |
|---|
| E3 | =CountDupes(A1:C9,0,1) |
|---|
<thead>
</thead><tbody>
</tbody> |
<tbody>
</tbody>
