3

I have an Excel Spreadsheet that looks more or less like this:

+------------+-----------+
| First Name | Last Name |
+------------+-----------+
| John       | Paul      |
| Joe        | Jones     |
| John       | Paul      |
+------------+-----------+

What I need to be able to do is detect the amount of duplicate entries and show a number of rows where the first and last name were in there at least twice.

So basically I'm looking for how many people in the list were in the list at least twice.

3 Answers 3

1

Here are some methods you can try.

Using An Array Formula

Assuming first names are in col A and last names are in col B (with A1 and B1 containing the header labels "First Name" and "Last Name"), insert the following formula into C2.

=SUM(($A$2:$A$11=A3)*($B$2:$B$11=B3))

After typing the formula, press Ctrl +Shift + Enter to enter it as an array formula. Copy it down to the blank cells below it. The formula will show you how many times a name combination turns up in your data. You can then just use Conditional Formatting to more visibly see which people are listed in your table at least twice.


Using COUNTIF & Another Column

Assuming first names are in col A and last names are in col B (with A1 and B1 containing the header labels "First Name" and "Last Name"), insert the following formula into C2 and copy it to the cells below it.

=A2&B2

You'll get something like "JohnPaul" or "JoeJones." You can then just hide this column later.

In cell D2, enter the ff. formula. Copy it down to the other cells below it.

=COUNTIF($C$2:$C$11,C2)

You'll see the same result as that of the array formula above.


Using the COUNTIFS Function & Conditional Formatting

I believe this function is only available in Excel 2007 and above.

Let's say first names are in A and last names are in B.

Highlight both columns then go to Conditional Formatting.
Create a new rule >> Use a formula to determine which cells to format
Enter the ff. formula:

=COUNTIFS($A:$A,$A1,$B:$B,$B1)>=2

Set your preferred format then click OK.
On the Conditional Formatting Rules Manager window, set Applies to to =$A:$B

enter image description here

0

If I were tackling this, I would first sort the rows so my last name then by first name so that all duplicates would be next to one another.

Lets say that first names are in column B and last names are in column C. Let's also assume that the first data row is row 2. Lets also assume that we'll put the running counts of duplicates in column A.

Next, in CELL A2 put the following formula:

=IF(AND(B2=B1,C2=C1),A1+1,1)

Now, copy A2 and paste it down column A so that this formula is beside every name in your list.

If you've done all this correctly, column A will now contain a running count of duplicates. If you want to detect them more easily, put conditional formatting on column A so that values > 1 will stand out.

Good luck!

0

To get the count of values with duplicates in the list, use the following formula:

=COUNTA(A1:A20)-SUM(IF(COUNTIFS(INDIRECT("$A$1:" & ADDRESS(ROW(A1:A20),1)),A1:A20,INDIRECT("$B$1:" & ADDRESS(ROW(B1:B20),2)),B1:B20)=1,1,0))

Enter this as an array formula by pressing Ctrl+Shift+Enter. This will give you the count in one cell -- no extra columns needed.

You can also return the row numbers of each duplicate entry, but you'll need a little help from a VBA user-defined function (UDF). To create the UDF in VBA, press Alt+F11 and insert a module. Paste the following code into the module.

Public Function CCARRAY(rr As Variant, sep As String)
'rr is the range or array of values you want to concatenate.  sep is the delimiter.
Dim rra() As Variant
Dim out As String
Dim i As Integer

On Error GoTo EH
rra = rr

out = ""
i = 1

Do While i <= UBound(rra, 1)
    If rra(i, 1) <> False Then
        out = out & rra(i, 1) & sep
    End If
    i = i + 1
Loop
out = Left(out, Len(out) - Len(sep))

CCARRAY = out
Exit Function

EH:
rra = rr.Value
Resume Next

End Function

Now, to get the list of row numbers of duplicates, enter the following formula in a cell:

=CCARRAY(IF(COUNTIFS(INDIRECT("$A$1:" & ADDRESS(ROW(A1:A20),1)),A1:A20,INDIRECT("$B$1:" & ADDRESS(ROW(B1:B20),2)),B1:B20)>1,ROW(B1:B20),0),", ")

Enter this formula as an array formula by pressing Ctrl+Shift+Enter.

You can also use this UDF to get a list of the actual values that have duplicates. The following formula will work for this purpose:

=CCARRAY(IF(COUNTIFS(INDIRECT("$A$1:" & ADDRESS(ROW(A1:A20),1)),A1:A20,INDIRECT("$B$1:" & ADDRESS(ROW(B1:B20),2)),B1:B20)>1,A1:A20&" "&B1:B20,0),", ")

Also enter this as an array formula.

Screen capture

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .