98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsForum home » Delegate support and help forum » Microsoft Excel Training and help » best training excel intermediate - Duplications
best training excel intermediate - Duplications
Resolved · Low Priority · Version Standard
John has attended:
Excel Advanced course
Duplications
Hi, Please could you help. I have a sheet of data and want to quickly identify and duplications so that they can be removed. What is the quickest way of doing this?
Many thanks.
John
RE: Duplications
Hi John
there are a number of ways. you can use the countif formula to count the number of times each data appears in a named range.
=sumif(range,criteria)
so you want to put (in cell B1) =sumif(A:A,A1) to find the number of times the data in cell A1 appears in column A. Drag this formula down and then you can find the occasions where cell Bx is greater than 1
you could use conditional formatting to do this.
Format > conditional formatting
select 'cell value is greater than' and type 1 in the box next to it. Then edit the format how you wish, eg a red background for any occurences of where this is true.
Alternatively you could give auto filter a go once you've done your sumif. to do this, highlight all cells in the sheet by clicking the box to the left of the A column header and above the 1 column header, select Data > filter > auto filter then you can select anything that is not a 1 in the sumif column.
Hope this helps?
Paul
Training information:
See also:
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Paste a web address into the hyperlink address fieldIf you copy a web address the only way to paste into the address field of the hyperlink box is to use CTRL + V. Right click paste does not work. |