Duplicate entries in more than one column

[ZoR]

hellor,

Well, i hope someone here will be able to help me!

My data is as follows:

LAST NAME FIRST NAME

1. ABC OPP
2. XYZ MGF
3. RTY SED
4. QRS WER
5. ABC OPP
6. RTY SED
7. QFG CVT
8. POL MUH


I want to identify the duplicate entries by grabbing the data from both
columns that is Last Name and First Name as well.
So that I get rows 1 &5 as well as rows 3 &4 highlighted.

All the help information that you have given in the community is for data in
ONe COLUMN ONLY.
You will be of great help if you could add a solution to my problem as well!

Looking forward to your reply

 

[Dave Peterson]

I'd use a helper column and concatenate the first and last names with a unique
string:

=a1&"..."&b1
and drag down

Then look for duplicates in this column.

 

[ZoR]

Hello Dave,

Thanks for your help.
Concatenating the data is a good idea but my records are more than 5000!
Also, is there anyway to copy a formula to a range of cells without having
to drag the formula all the way DOWN to all the records?

Concatenating works for small number of records. With a large number, i
don't think it will be easy to scroll up and down to see which ones are
duplicated or not!

 

[Gord Dibben]

You can copy a formula down by double-clicking on the fill handlel(small black
square at bottom right corner of cell)

Will copy down as far as you have data in an adjacent column.

What do you want to do with the duplicates?

You can get a list of all the Uniques by Data>Filter>Advanced Filter>Unique
records only and copy to another location.

See Chip Pearson's site for more methods of finding duplicates.

http://www.cpearson.com/excel/Duplicates.aspx


Gord Dibben MS Excel MVP

 

[ZoR]

hello Gord,

Thanks for helping out with the copy paste thing.

For duplicates, I have more than 5000 records with peoples first and last
name in two seperate columns.
i want to identify those whose names have been duplicate or have multiple
entries.
i would prefer highlighting all duplicate entries as well as their
DUPLICATES with respect to their FULL NAME i.e. grabbing data from both
columns and then comparing it with all remaning rows.
please help me out there!

thanks.

 

[Gord Dibben]

I thought you were going to use Dave's formula =a1&"..."&b1 to give you a third
column to filter or flag per Chip's methods.

After doing that, copy that column and Paste Special>Values>OK>Esc.

Chip's site is a good place to start for flagging duplicates.


Gord

hello Gord,

Thanks for helping out with the copy paste thing.

For duplicates, I have more than 5000 records with peoples first and last
name in two seperate columns.
i want to identify those whose names have been duplicate or have multiple
entries.
i would prefer highlighting all duplicate entries as well as their
DUPLICATES with respect to their FULL NAME i.e. grabbing data from both
columns and then comparing it with all remaning rows.
please help me out there!

thanks.