Microsoft QuickTips

Which l does one use to detect duplicate numbers when editing with excel? is it possible for them to be detected as you enter them or does one have to wait until sheet is completed and actually check the numbers against each other bloke ually? i have tried using sort by putting numbers in numerical order to make it easier to spot duplicate entries, but this process unfortunately then disrupts the sequence of the info in the cells on the right. when i go to undo it appears that much time has lapsed and i get cannot undo. it's so time consuming and tiring checking a list of 100 telephone numbers before i can email them. the numbers, regions and source is copied and pasted onto my excel in an orderly bloke ner with all numbers from the same region being together, but i need to find a way of checking duplicates without changing all cells on the right. my sincere thanks to anybody who can help and excuse the stupidity, but i am still trying to self learn how to bloke age excel.

This looks like a long answer, but i'm being thorough and the 2nd part is an optional extra thing you could do. i take it that these numbers vary, but are consistent in their formatting (spaces and dialling codes). a simple method would be as follows: ----------------------------------------------------- part 1. in your spreadsheet, type this in the next empty column available (preferably after your phone number column and on the same row as your first phone number entry): =countif($a$2:$a$100,a2) where: a is the column that will contain your phone numbers 2 is the row that contains the first of your phone numbers 100 is the row that contains the last of your phone numbers a2 is the cell that holds first phone number. all being well, the cell with the formula you have typed in should now contain a zero. let us assume that you have typed this in cell b2. what you want to do now is to copy that formula in b2 and paste it into the cells underneath it (b3) all the way down until the last row as your last phone number - in this case, it would be cell b100. (copy the cell and then highlight the range of cells you want to paste into and hit enter). now start typing your phone numbers starting from cell a2 (or wherever the formula points to). you will see your formula cell change from zero to 1. basically, excel is checking your numbers against the others in that column. keep typing phone numbers and one time excel spots a duplicate, the formula cell will change from zero to 2, instead of one. so, now you know that's a duplicate. ----------------------------------------------------- part 2. you don't say what you want to do with the number if you find a duplicate. i'm assuming that you only want to prevent duplicates appearing in your sheet, so you will probably delete that entry and move on to the next number. what if you wanted to eradicate the other phone number? how do you find that one in a list of 100's? well, the duplicate will be the other entry that also has a 2. if you also want to find that one, then you will need to set up a filter. make sure that your data has headings/titles in the top row (number, region, source, etc) and then click on any cell on the top row and then in the menu, click on data/filter/autofilter. your top row should now have little downward arrows in them. click on one of the arrows and a list will pop up. this list will contain all the entries in that column. so if you click on the arrow in your phone checking column (column b), you should only see zeros, ones (and twos if you have duplicates). click on one of the list items and excel will only show them. by clicking on the 2's, you can see your duplicates together. to get back to your whole list, click on the arrow and click on (all). or on the menu, click on data/filter/show all. one final thing: if you're going to sort data, make sure you highlight all your data before doing so. i reckon that's where you're losing the order of the cells on the right, because you may not be including these in the sort. failing that, another solution is to fill an empty column with consecutive numbers. if you include this in your initial sort range, you will be able to get the original order back by sorting through this column again. 'hope that helps. r