Yes I should know this.

If I have a column of data, what's the best way of checking for duplicated data? Somehow using the count function or a pivot table?

### Replies (9)

### Please login or register to join the discussion.

Pivot Table or ASAP Utilities

Create a pivot table and drag the column into the rows area and the values area. Value field setting should be set to "count" (Right click on heading). Duplicated values will have a count of more than 1.

ASAP Utilities is an add in that can check for duplicated values and delete.

A Non-Techie solution!

Copy and paste/value the data into another column, then sort by value.

In the next column deduct the second figure from the first.

Copy this formula down the entire column. If you get any zeros you have a duplicate.

With COUNTIF

And assuming what you want to check is in Col A you could use:

=COUNTIF(A:A,A2)

Or perhaps Conditional Formatting for Duplicate Values

Both...

PivotTable as Stevie said, or:

=COUNTIF(A:A,a2) - assuming data is in column A and the headers are in row 1 - and drag down the whole way. Add a filter to the colum and de-select 1 from the filter list (assuming post-2003 version of Excel). Added advantage if this approach is that you then also see all of the duplicate entries to review.

Never used it, but Excel 2010 has a 'remove duplicates' button in the Data section?

Cheers

Pat

It depends where you want to see the results

I agree with Stevie in that a pivot table will show the result of the count. Richard's solution will also work, but only with numerical data - if you're trying to identify duplicated text then it won't.

However, if you want to see duplicates directly on the data entry worksheet, then you can insert a column and then use the COUNTIF formula (i.e. counting the number of entries in the column with the cell you are looking at. E.g. COUNTIF($B:$B,A1) if your data is placed in column B .

Then copy this formula down to the end of the data you are looking at. Any duplicates will obviously not return an answer of 1. A further visual aid would then be to also add conditional formatting to perhaps shade any cells not returning the answer of 1 an easily-noticeable colour (e.g. red, orange)

EDIT: Drafted but not posted before ACDWebb and Pat's responses. Nice to see like-minded thinking!

Condtional formatting - Use Duplicate values

If you have xl2007 or xl2010, then the Conditional Formatting/Highlight Cells Rules/Duplicate Values can be used.

This works on duplicate values - be they either text or values.

Excel 2010 - Remove Duplicates

Hi

In Excel 2010 there is a built in function for this - on the Data Tab. The function is called Remove Duplicates.

Regards

Elliott

SUMIF

Copy and paste/value the data into another column, add another colum, type and drag 1. e.g column a is ur data with 100 cells, column b should only have 1 written next to each data. leave cell a1 empty.

In the third column type =SUMIF($a$2:$a$101,a2,$b$2:$b$101), drag this formula from cell 2 to cell 101.

If you get any 1 you have not duplicated, if you get more than 1 then meaning duplicated.

Sort column b based from smallest to biggest

look for ones which dont have 1, meaning non duplicates

In Excel 2010 there is a built in function for this - on the Data Tab. The function is called Remove Duplicates. Double empty cells are treated by excel as duplicates. I would suggest you should use conditional formatting as it automatic and can cope with multiple rows and columns, but if you do this with countifs functions, it looks untidy. You do not have to drag with CF as soon as you type a duplicate, it will be highlighted