# Identify duplicates across rows and columns

#### Dicegirly

##### New Member
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
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
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

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

### 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...