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!

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!
==============================================


"Amie G" wrote in message
...
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!

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

"Amie G" wrote:

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!

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!




"RagDyer" wrote:

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!
==============================================

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!


"Nikki" wrote:

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

"Amie G" wrote:

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!

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


Amie G Wrote:
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!


"Nikki" wrote:

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

"Amie G" wrote:

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!



--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=484467

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!
==============================================


"Amie G" wrote in message
...
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!


"Nikki" wrote:

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

"Amie G" wrote:

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!

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!
==============================================


"RagDyer" wrote in message
...
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!
==============================================


"Amie G" wrote in message
...
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!


"Nikki" wrote:

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

"Amie G" wrote:

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!

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

"Amie G" wrote:

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!

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 you
need to keep then perhaps auto-deleting might present more problems.

Another possibility is then working on from the sort idea, and
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 over
itsself.
You now have each row numbered.

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

Sort over C and then B

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

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

Just another thought . . .



pameluh Wrote:
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 :)

"Amie G" wrote:

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!



--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=484467

Brian is correct. My method will only produce unique records (by row, not by
column).

"Bryan Hessey" wrote:


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 you
need to keep then perhaps auto-deleting might present more problems.

Another possibility is then working on from the sort idea, and
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 over
itsself.
You now have each row numbered.

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

Sort over C and then B

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

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

Just another thought . . .



pameluh Wrote:
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 :)

"Amie G" wrote:

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!



--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=484467

Thanks Bryan,

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

Thanks!



"Bryan Hessey" wrote:


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


Amie G Wrote:
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!


"Nikki" wrote:

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

"Amie G" wrote:

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!



--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=484467

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!



"RagDyer" wrote:

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!
==============================================


"RagDyer" wrote in message
...
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!
==============================================


"Amie G" wrote in message
...
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!


"Nikki" wrote:

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

"Amie G" wrote:

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!

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!



"pameluh" wrote:

Brian is correct. My method will only produce unique records (by row, not by
column).

"Bryan Hessey" wrote:


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 you
need to keep then perhaps auto-deleting might present more problems.

Another possibility is then working on from the sort idea, and
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 over
itsself.
You now have each row numbered.

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

Sort over C and then B

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

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

Just another thought . . .



pameluh Wrote:
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 :)

"Amie G" wrote:

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!



--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=484467

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!
================================================== ===

"Amie G" wrote in message
...
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!



"RagDyer" wrote:

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!
==============================================


"RagDyer" wrote in message
...
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!
==============================================


"Amie G" wrote in message
...
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!


"Nikki" wrote:

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

"Amie G" wrote:

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!