Forum home » Delegate support and help forum » Microsoft Excel Training and help » best training excel intermediate - Duplications
|
|
|
The UK's most regular 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. |
best training excel intermediate - Duplications
Resolved · Low Priority · Version Standard
Reply Mon 12 Nov 2007, 16:50Delegate John said...
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
For upcoming training course dates see: Pricing & availability
Reply Tue 13 Nov 2007, 14:50Delegate Paul said...
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
|