How do I locate duplicate records in Excel?

A

Amie G

I'm trying to locate duplicate records in my spreadsheet. It has 15 columns
and almost 3000 rows. Row B is Last Name and Row C is First Name. I need to
highlight any records that have the same last and first names. So if there
are multiple Bob Jones I want to check and see if they are the same person.

How do I do this? I've tried all kinds of methods suggested on the web and
have not been able to find a solution. Thanks!
 
R

RagDyer

Check out Chip Pearson's web site:

http://www.cpearson.com/excel/topic.htm

Scroll down to the D's, and see all the pages on handling duplicates.
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================
 
N

Nikki

I would make a column combining name and last name in column D, =B&C and then
would use conditional formatting formula is highlight the column go to format
conditional formatting chnage cell value is to formula is and use this
formula and =COUNTIF($d$1:$d$14,d1)>1 click on format and choose a color.

Nikki
 
A

Amie G

Thanks RD!

I've already been to his site and tried multiple methods he suggests, but
haven't had any luck. The "highlighting" instructions don't apply to my
scenario because his example only looks for duplicates within one single
column.

It seems like perhaps one of the bottom options might work but I don't know
which one or even what exactly he's saying to do. I'm not familiar with
Excel terminology so I don't really understand.

It seems like I might have to combine a few of his suggestions since the
"extracting" ones at the bottom don't say anything about highlighting.

Any further suggestions? I appreciate it!
 
A

Amie G

Thanks Nikki.

I've tried your suggestion but I must be doing something wrong because I'm
still not getting anything. I even made a small sample set with fake
duplicates just as a test. It seems like I'm doing exactly what you
suggested, but I've tried so many suggestions now with no success that I
think I must be repeatedly missing some critical step somewhere.

Is someone able to walk me through step by step?

Thanks!
 
B

Bryan Hessey

Nikki,

The suggestions do work, but for you to test, after the D column is set
with the combined C & B data, then in column E try:

=IF(COUNTIF(D$1:D$99,D1)>1,COUNTIF(D$1:D$99,D1),"")

and formula-drag that to the bootom row of your data.

and you should get a blank or a number of duplicates listed in that
column.

if you have data problems using =C1&" "&B1 then you might try
=TRIM(C1)&" "&TRIM(B1)
to give a correct looking name format.

Once you have a count in column E you can try the conditional format
either in the same formula or, more simply, flag column E on a
non-blank.

Hope this helps
 
R

RagDyer

Try this for a test:

Select B2 to B30, then,

<Format> <ConditionalFormat>
Change "Cell Value Is" to "Formula Is",
And enter this formula:

=SUMPRODUCT(--(B2:C2=$B$2:$C$30))>2

Click on "Format" and choose a loud red font and bright yellow pattern
color, then <OK> <OK>.

Now, make sure you have some duplicates in B & C.

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================
 
R

RagDyer

I pasted the wrong formula!

Use this one instead:

=SUMPRODUCT((B2=$B$2:$B$30)*(C2=$C$2:$C$30))>1

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================
 
P

pameluh

First sort your data by last name, then by first name

Highlight all rows | Data | Sort | First by Column B | Then by Column C

Now your list is in alphabetical order by last name.

Highlight all 15 columns | Data | Filter | Advanced Filter | Copy to new
location | Copy to: (click on the little icon and click on the first row in
the first empty column to the right of the data | Check the box that says
Unique Records Only | Ok | Delete the old data, the new "copied" data will
not contain duplicates.

Pamela :)
 
B

Bryan Hessey

Amie,

Pamela's suggestion is quite good, but I note that you have 15 columns
two of which are name, and if the other 13 columns have data that yo
need to keep then perhaps auto-deleting might present more problems.

Another possibility is then working on from the sort idea, an
presuming column Q is unused, in Q1 put

=Row()

and formula-copy that to the end of your 3,000 rows.
Highlight column Q and COPY, then Paste Special = Values back ove
itsself.
You now have each row numbered.

Select all data (the cell to the left of A in the column headers, an
above row 1), all your data should highlight.

Sort over C and then B

You can then manually inspect, merge, delete, amend and fix a
required. (these are 'names' and Robert = Bob etc)

After you are complete re-sort the sheet over column Q ascending an
delete column Q.

Just another thought . . .
 
A

Amie G

Thanks Bryan,

Where do I put the formula? Do I select the first cell of the D column or
the entire range?

Thanks!
 
A

Amie G

Thanks so much RagDyer! It worked!

Now the only question is how to I do this for the entire list without
holding down the scroll bar forever? Is there a shortcut to tell the
computer which cells to select. My computer is slow!

Thanks again! I really appreciate it!
 
A

Amie G

Thanks Pameluh and Bryan!

It seems that RagDyer's suggestion worked for me! I wanted to see all the
duplicate records within the list without deleting them. My only question
now is how do I apply these formulas to such a long list of 3000 records
without having to scroll down forever. My computer is really slow!

Thanks again!
 
R

RagDyeR

If you're referring to the first instruction to select B2 to B30,
Where you wish to select, say B2 to B3000, try this:

Click in B2,

Click in the NameBox (left of the formula bar),

Type in
B3000

Hold down <Shift>

Hit <Enter>

And you should now have your entire range selected.

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

Thanks so much RagDyer! It worked!

Now the only question is how to I do this for the entire list without
holding down the scroll bar forever? Is there a shortcut to tell the
computer which cells to select. My computer is slow!

Thanks again! I really appreciate it!
 
Top