Solved How To Find Duplicate Records

Dell INSPIRON 1525
February 27, 2013 at 04:38:25
Specs: Windows 7, 2.128 GHz / 1014 MB
A 1	B	C	D	E	F	G
A 2	1	14556	James	John	McCabe	McCabe1
A 3	2	52250	John	Peter	Brown	0
A 4	3	58909	Andrew	Michael	Peters	0
A 5	4	34456	James	James	Jones	0
A 6	5	32534	Neil	Ron	West	West1
A 7	6	34344	Neil	Philip	Smith	0
A 8	7	63465	Peter		Jones	0
A 9	8	14556	James	John	McCabe	McCabe2
A 10	9	33434	Mike 	Steven	Simpson	0
A 11	10	43545	Walter		Lee	0
A 12	11	44354	Andrew	Michael	Peters	0
A 13	12	32534	Neil	Ron	West	West2

						Match Found C,D.E,F
Hi Can anybody help with problem, I have put in some random data

The record number is in Col B with a ID number in Col C and the names in Col D,E and F.

Records 1 and 8 and records 5 & 12 are obviously the same or dulplicates, if I can get the output in Col G, I can then data sort Col G then look at the data to see if they are in fact duplicates.

I have no idea what the best process would be for this.

Many Thanks

See More: How To Find Duplicate Records

Report •

February 27, 2013 at 08:27:43
Col C are their ID numbers,
are they not unique to each individual?

You can sort your whole matrix of data on column C
and each duplicate will be next to each other.

Or you can use the Remove Duplicate function.

Select your range of data, A1 to G13 (I think)
On the Ribbon, select Data Tab
Select Remove Duplicates
Click on the UN-Select All button
Then select:
Column C - ID numbers
Column D - Last Name
Column E - First Name

When you click OK, the duplicates will be removed
and a message box telling you how many duplicates
were removed will pop-up


Report •

February 27, 2013 at 08:40:39
A straightforward method is to sort on col . C , examine the rows with the same ID number now clustered and then sort again on col. B.

Report •

February 28, 2013 at 01:44:21
✔ Best Answer
Ref How To Find Duplicate Records

Many thanks for your reply; my problem is there are over 20.000 records I have at this stage come up with a solution

I Added the number and three names together using =C4&D4&E4&F4

I then get 14556JamesJohnMcCabe

I then used the formula =IF(COUNTIF($H$4:H4,H4)>1,"Duplicate Name",0)

If I populate this formula down I can find the duplicates.

If I Data sort Col C I then get all the duplicates with the same ID number grouped together, so it’s easier to remove what are 100% duplicates.

The problem yet to overcome is what constitutes a duplicate for example if the middle name is missing in another record, it will not return a duplicate, although it’s more than likely is.

Report •

Related Solutions

Ask Question