When you have a big Excel/Calc spreadsheet, you often need to see if you have duplicate entries in a column. I also wanted to find out if there's any way to mark the corresponding rows if different columns hold duplicate entries. I did not find any direct solution to this, I had to do it in a few steps.
You have to select the all the entries (click on the first element of the first row, then press ctrl+shift+End), then sort them in ascending order (go to Data->Sort, select the column you want to find the duplicate in, then press OK). Make a new column. For example, if the column that you want to search for duplicate in, is A, and the new column is E, and if the entries start from A2, then put this formula in E2: "=IF((LOWER(A2)=LOWER(A1))OR(LOWER(A2)=LOWER(A3));1;0)". Set the formula to the whole "E" column. It will show 1 if it is duplicate entry, 0 otherwise.
Now, again select the whole range of data, then go to Format->Conditional Formatting. Under Condition1, select "Formula is", and then enter this formula: "=INDIRECT("E"&ROW())=1", then select the format you want the duplicates rows to show. Thats all, now you'll easily find out the duplicate entries.
If anyone know the direct solution to do this, please tell me.......
No comments:
Post a Comment