Identify duplicates across rows and columns

Dicegirly

New Member
Joined
Oct 26, 2015
Messages
10
Hello,
I have looked at other posts along these similar lines but I can't see anything that answers what I want.

My data has the following columns:
Column A = Surname
Column B = Forename
Column F = Date of last attendance

Someone has very kindly chucked the data from several spreadsheets into one sheet which has left a lot of duplications for me to sort out.

What I would like to do is to identify duplicated rows of data.
It is possible that all 3 of these columns can contain duplicates, but not necessarily be a duplicated row (as people can have the same names, or have the same date of last attendance).

What I need is a formula where it will look for a duplicate where the whole row in Columns A, B and F are duplicated in another row, not just individual cells.

I have tried all kinds of things to get it to identify them for me, but I can't think how to do it for the whole row.

I am currently working through a list of 16000+ rows and manually removing lines - but there must be a better way?!

Does anyone have a suggestion please?
 

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,327
Sort the data by columns A B and F
In a blank cell in row 2 put

=AND(A2=A1,B2=B1,F2=F1)
and copy the formula down the column

Since the rows are sorted all the similar data will be should be grouped together.

A 1 in the new column you entered will indicate a duplicate of all columns A B and F

You may get problems with things like Jon Smith, John Smith, J Smith where they are the same person but theres no way round that.
 

Dicegirly

New Member
Joined
Oct 26, 2015
Messages
10
Sort the data by columns A B and F
In a blank cell in row 2 put

=AND(A2=A1,B2=B1,F2=F1)
and copy the formula down the column

Since the rows are sorted all the similar data will be should be grouped together.

A 1 in the new column you entered will indicate a duplicate of all columns A B and F

You may get problems with things like Jon Smith, John Smith, J Smith where they are the same person but theres no way round that.
This is fantastic! Thank you :biggrin:
 

Forum statistics

Threads
1,081,415
Messages
5,358,533
Members
400,502
Latest member
price83

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top