I have a sheet with hundreds of 4 digit numbers in as many as 20 columns in it. Sometimes we make mistakes and type the same number in different columns. That should not happen because these are Employee Numbers in a company and this sheet is the attendance sheet for that particular day. How can I find if I have made double entries of the same number by mistake?
Last edited by VBA Noob; 03-16-2009 at 03:22 PM.
One method is to use conditional formatting to highlight any entries that have already been entered.
Steps:
1. Highlight the entire range that you want to query.
2. Go to FORMAT-CONDITIONAL FORMAT
3. Select the "Formula Is" from the dropdown box
4. Enter the following formula (note: the cell reference will change based on the FIRST cell in your range): =COUNTIF($B$4:B4,B4)>1
5. Select the button format and choose a color.
Report back if that helps.
Hi and welcome to the board.
Suppose your range is A1:d50
Select A1
Select menu Format - Conditional formatting
Change the first drop down box to "Formula is"
As formula enter =countif($a$1:$d$50,a1)>1
Select tour formatting -OK
Select paint brush
Paint your range
All duplicates are now highlighted with the format you've chosen
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks