Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How Do I find distinct values in a Column
Hello,
I work in the Resourcing Dept. i have to prepare a list of candidates that were interviewed. I have to consolidate lists from four offices. I have come across duplicate candidates entries after consolidating the worksheets. Can anyone tell me how do I find out duplicate values. Thanks |
#2
|
|||
|
|||
Method 1
Click on <Data<Sort and sort on the column which contains possibly duplicated data. This will bring possible duplicated records together so that they may be inspected and deleted as appropriate. It would help if each record contains a unique reference number (URN) so that the database may be resorted into its 'proper' order. Method 2 Click on <Ctrl AND <F and type in the significant part of possibly duplicated data (This may be as few as five characters) Click on <Find all This will list those cells which contain data from possibly duplicated records. Regards. Bill Ridgeway Computer Solutions "Stankov" wrote in message ... Hello, I work in the Resourcing Dept. i have to prepare a list of candidates that were interviewed. I have to consolidate lists from four offices. I have come across duplicate candidates entries after consolidating the worksheets. Can anyone tell me how do I find out duplicate values. Thanks |
#3
|
|||
|
|||
Bill's suggestions are excellent for finding duplicated records. If you wish
to expunge duplicates and have a list with names appearing only once: Insure a header cell is on top of the column of names, select the column and pull-down: Data Filter... Advanced Filter and check unique records only. -- Gary''s Student "Stankov" wrote: Hello, I work in the Resourcing Dept. i have to prepare a list of candidates that were interviewed. I have to consolidate lists from four offices. I have come across duplicate candidates entries after consolidating the worksheets. Can anyone tell me how do I find out duplicate values. Thanks |
#4
|
|||
|
|||
Bill,
Yes your suggestion work. However, going forward I get 2000 records every week and that would be a problem to go thru it manually. I need to update the sender on the duplicacy. Is there a way whereby i dont have to check the same manually. Regards S Stanly "Bill Ridgeway" wrote: Method 1 Click on <Data<Sort and sort on the column which contains possibly duplicated data. This will bring possible duplicated records together so that they may be inspected and deleted as appropriate. It would help if each record contains a unique reference number (URN) so that the database may be resorted into its 'proper' order. Method 2 Click on <Ctrl AND <F and type in the significant part of possibly duplicated data (This may be as few as five characters) Click on <Find all This will list those cells which contain data from possibly duplicated records. Regards. Bill Ridgeway Computer Solutions "Stankov" wrote in message ... Hello, I work in the Resourcing Dept. i have to prepare a list of candidates that were interviewed. I have to consolidate lists from four offices. I have come across duplicate candidates entries after consolidating the worksheets. Can anyone tell me how do I find out duplicate values. Thanks |
#5
|
|||
|
|||
You as << Is there a way whereby i dont have to check the same manually.
There is an important requirement to ensure the integrity and reliability of data. To do that you need to ensure that ALL data on possibly duplicated records is exactly the same. There is a possibility that data in certain fields are more up-to-date than others. There is, therefore, a need to inspect all possibly duplicated data. Assuming that because data in one or two fields are the same, the rest is going to be duplicated is downright dangerous as once deleted it may not be recoverable. How much data from the 2000 records every week is actually duplicated (Rhetorical)? If it is a lot it would be in the interest of everyone to look at and change your working systems to reduce (if not eliminate) the duplication at source. Reducing tedious unnecessary work would increase efficiency. If you are not getting much duplication or just cannot stem the flow you could resort to a semi-automatic method. Add a helper field - =if(and(a2=a1,b2=b1,c2=c1...),"Dup",) You are limited to the number of arguments so you may not be able to check all fields. To do that you'll need a second (third) helper field (to cover the ones not covered by the previous ones and a last to check if either the first ones (or three) have returned a "Dup". Sort data by the three most significant fields. Goto and delete the duplicates. As you will appreciate this is not pretty and involves some effort in setting up. As with other things, the answer is yes, it can be done but are you willing to accept the price. Good luck. Bill Ridgeway Computer Solutions "Stankov" wrote in message ... Bill, Yes your suggestion work. However, going forward I get 2000 records every week and that would be a problem to go thru it manually. I need to update the sender on the duplicacy. Is there a way whereby i dont have to check the same manually. Regards S Stanly "Bill Ridgeway" wrote: Method 1 Click on <Data<Sort and sort on the column which contains possibly duplicated data. This will bring possible duplicated records together so that they may be inspected and deleted as appropriate. It would help if each record contains a unique reference number (URN) so that the database may be resorted into its 'proper' order. Method 2 Click on <Ctrl AND <F and type in the significant part of possibly duplicated data (This may be as few as five characters) Click on <Find all This will list those cells which contain data from possibly duplicated records. Regards. Bill Ridgeway Computer Solutions "Stankov" wrote in message ... Hello, I work in the Resourcing Dept. i have to prepare a list of candidates that were interviewed. I have to consolidate lists from four offices. I have come across duplicate candidates entries after consolidating the worksheets. Can anyone tell me how do I find out duplicate values. Thanks |
#6
|
|||
|
|||
Just had another thought. A macro could possibly do the job of comparing
data and deleting duplicated records. Although easy to write and run it does rely on the "the computer done it so it must be OK" mentality which can be dangerous if the macro is not 100% foolproof and important data is deleted in error. Regards. Bill Ridgeway Computer Solutions |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Intervals of Filtered TEXT values in Column and Return Count across a Row | Excel Worksheet Functions | |||
Arithmetical Mode of Criteria in Multiple Non-Adjacent columns | Excel Worksheet Functions | |||
is it possible to Restrict Find & Replace to 1 column | New Users to Excel | |||
search a row to find the column | Excel Worksheet Functions | |||
How sum values in column B using values in column A as the conditi | Excel Worksheet Functions |