Discussion Locked

find duplicate entries in Excel

0 helpful
9 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Does anyone know how to find the Excel records which have identical entries in one of the columns?

Thanks
Ewa

9 replies

by gurnerworld

Ewa,

I'm not sure if I understand the question correctly, but if you want to identify which columns have more than one identical entry, you could do the following:

1) Sort the list by the relevant column
2) Use the subtotals caommand from the data menu - apply a count of the relevant column at each change in the relevant column
3) Click the number 2 heading in the outline

This would provide an at a glance method of identifying which entries have duplicates (as anything with a duplicate would show a count of more than one).

Hope that's of some use to you (although I'm sure there ought to be a better way).

by theophotech

If you are trying to match row entries in two columns, that are "supposed" to be indentical, you can highlight each column, hit F5, click custom and choose row differences. With the cells selected now, format them with a color to highlight the differences. If you want a better way. I have a macro that does does disparate lists.

by larry h.

Sort the rows, then in the second row (somewhere to the right of the last used column) type a formula similar to the following:
=IF((A1:D1)=(A2:D2),"Duplicate","")
The above assumes that columns A through D are used. This is an array formula, soin order to get it to work you must type it in and complete it by holding down the CTRL and SHIFT keys as you press ENTER. When you view it in the formula bar you will see:
{=IF((A1:D1)=(A2:D2),"DUPLICATE","")}
indicating the array formula.

You can then copy and paste (or AutoFill) this formula down to the rest of your rows. If two consecutive rows are identical the formula will display the word DUPLICATE, otherwise the cell will be blank. You can then either scan down the list and lookfor "DUPLICATE" or you can select the column, copy, and paste special, choosing to paste the values. This would allow you to re-sort the list base on the DUPLICATE column for easy deletion.

by jim_rau

Here is a thought (my group likes this) ...

Sort your list by the column you want to check. The in the first entry choose conditional formatting. Make the condition (assuming you sorted on column A) If A2=A3 and choose (if the condition is true) to change the format to bold. Paste this format on the entire column and as you look down the list the first time a number appears in the list it will be obvious.

Or use <> (not equal) to highlight only duplicates.

I hope this was useful info ... good luck!

by larry h.

Sort the rows, then in the second row of data (somewhere to the right of the last used column) type a formula similar to the following: =IF((A1:D1)=(A2:D2),"Duplicate","")

The above assumes that columns A through D are used. This is an array formula, so in order to get it to work you must type it in and complete it by holding down the CTRL and SHIFT keys as you press ENTER. When you view it in the formula bar you will see:
{=IF((A1:D1)=(A2:D2),"DUPLICATE","")}
indicating the array formula.

You can then copy and paste (or AutoFill) this formula down to the rest of your rows. If two consecutive rows are identical the formula will display the word DUPLICATE, otherwise the cell will be blank. You can then either scan down the list and look for "DUPLICATE" or you can select the column, copy, and paste special, choosing to paste the values. This would allow you to re-sort the list base on the DUPLICATE column for easy deletion.

This discussion is closed to new replies.