How Do I find distinct values in a Column

[Stankov]

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

[Bill Ridgeway]

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" <Stankov@discussions.microsoft.com> wrote in message
news:B048AC88-5437-496B-B542-1AAECF755F66@microsoft.com...
> 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
>

[Stankov]

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" <Stankov@discussions.microsoft.com> wrote in message
> news:B048AC88-5437-496B-B542-1AAECF755F66@microsoft.com...
> > 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
> >
>
>
>

[Bill Ridgeway]

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" <Stankov@discussions.microsoft.com> wrote in message
news:FE19AF25-5B22-4B65-B5A9-8457255BC34D@microsoft.com...
> 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" <Stankov@discussions.microsoft.com> wrote in message
>> news:B048AC88-5437-496B-B542-1AAECF755F66@microsoft.com...
>> > 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
>> >
>>
>>
>>

[Bill Ridgeway]

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

[Gary''s Student]

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
>