Need to identify duplicate entries in a Table

[bman342]

Hi:

I am learning a lot by reading these posts, but I cannot figure this one out.

I have a table (nn rows x 6 columns).
The first column is the ID of the participant. The other five columns are
their selections from a validated drop-down (header row shown below). In each
row a participant selects five names from the same list (the list has about
1,000 names), but has to choose a different name for their five selections.
That part I have error checked.

Participant ID Name1 Name2 Name3 Name4 Name5

What I also need to check (which I cannot figure out) is that no
participants select the same five names from the list. While the five
selections are made from the same (alphabetized) list, they will not
necessarily be made in the same order.

Thanks for your help.

 

[Richard Buttrey]

Hi:

I am learning a lot by reading these posts, but I cannot figure this one out.

I have a table (nn rows x 6 columns).
The first column is the ID of the participant. The other five columns are
their selections from a validated drop-down (header row shown below). In each
row a participant selects five names from the same list (the list has about
1,000 names), but has to choose a different name for their five selections.
That part I have error checked.

Participant ID Name1 Name2 Name3 Name4 Name5

What I also need to check (which I cannot figure out) is that no
participants select the same five names from the list. While the five
selections are made from the same (alphabetized) list, they will not
necessarily be made in the same order.

Thanks for your help.

Does

Name1, Name2, Name3, Name4, Name 5
and
Name5, Name4, Name3, Name2, Name1

count as the same selection, or is it order dependent?

Rgds


__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

 

[bman342]

Yes. Counts as same selection. Not order dependent.

 

[Richard Buttrey]

One approach I can think of, and there are no doubt more elegant ways
that the usual suspects hereabouts can come up, with is as follows.

Use a couple of helper columns as follows.

The first column should concatenate the 5 names chosen by each
participant into one long string.

The second column would be the long string in the first column but
sorted alphabetically by character. A simple VBA macro could be
written to do this.

This second column could then be checked for duplicates by an Advanced
Filter to list somewhere else all the unique records. If there are the
same number of unique records in the filtered list as the original,
then you would know that all the choices were different. If there were
fewer records in the filtered list then it would mean that there was
at least one set of duplicates

If that sounds like an approach that would work with your data, then
let me know and I'll attempt to write a VBA macro to produce the
second sorted alphabetical string.

Rgds

Yes. Counts as same selection. Not order dependent.

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

 

[bman342]

Richard:

I like your solution, but you actually just gave me an idea for a relatively
easy, though imperfect solution. Since this is not a business-critical
application it will probably be sufficient.

How about adding up all the "match" values into a single column.
Then perform a conditional format on each cell to see if it equals any other
in the same column.
This will eliminate any cell not so-formatted, and will flag those that are
or may be duplicates.

 

[Herbert Seidenberg]

Another imperfect solution is to use a Pivot Table:
Multiple Consolidation ranges
Layout: Drag the Value button into DATA and ROW
Drag the Column button off COLUMN
Drag the Row button into COLUMN
Options: No totals
Then compare the columns of the Pivot Table with
=SUMPRODUCT(array C*ROW(array)^2)

 

[Richard Buttrey]

Hi,

I'm not quite clear what you mean by 'match' values.

I realised afterwards that whilst the way I suggested would work, it
wouldn't necessarily be foolproof. If one name were an anagram of
another, then my 'solution' would pick these up as being the same, and
report a duplicate when there wasn't.

Is your method working OK?

Rgds

Richard:

I like your solution, but you actually just gave me an idea for a relatively
easy, though imperfect solution. Since this is not a business-critical
application it will probably be sufficient.

How about adding up all the "match" values into a single column.
Then perform a conditional format on each cell to see if it equals any other
in the same column.
This will eliminate any cell not so-formatted, and will flag those that are
or may be duplicates.

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________