find duplicate entries in Excel
Does anyone know how to find the Excel records which have identical entries in one of the columns?
Thanks
Ewa
Windows Forum
Does anyone know how to find the Excel records which have identical entries in one of the columns?
Thanks
Ewa
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 ewa g
by theophotech
by ewa g
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 ewa g
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 ewa g
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.
Sort by Trending