Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need to identify duplicate entries in a Table
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need to identify duplicate entries in a Table
On Tue, 4 Jul 2006 07:30:02 -0700, bman342
wrote: 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 __________________________ |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need to identify duplicate entries in a Table
Yes. Counts as same selection. Not order dependent.
"Richard Buttrey" wrote: On Tue, 4 Jul 2006 07:30:02 -0700, bman342 wrote: 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 __________________________ |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need to identify duplicate entries in a Table
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 On Tue, 4 Jul 2006 08:40:02 -0700, bman342 wrote: Yes. Counts as same selection. Not order dependent. "Richard Buttrey" wrote: On Tue, 4 Jul 2006 07:30:02 -0700, bman342 wrote: 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 __________________________ __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need to identify duplicate entries in a Table
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" wrote: 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 On Tue, 4 Jul 2006 08:40:02 -0700, bman342 wrote: Yes. Counts as same selection. Not order dependent. "Richard Buttrey" wrote: On Tue, 4 Jul 2006 07:30:02 -0700, bman342 wrote: 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 __________________________ __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need to identify duplicate entries in a Table
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) |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need to identify duplicate entries in a Table
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 On Tue, 4 Jul 2006 19:41:01 -0700, bman342 wrote: 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" wrote: 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 On Tue, 4 Jul 2006 08:40:02 -0700, bman342 wrote: Yes. Counts as same selection. Not order dependent. "Richard Buttrey" wrote: On Tue, 4 Jul 2006 07:30:02 -0700, bman342 wrote: 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 __________________________ __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is there a macro to identify and remove duplicate data in Excel? | Excel Worksheet Functions | |||
Look up/math text | Excel Worksheet Functions | |||
Deleting duplicate entries in an Excel list | Excel Worksheet Functions | |||
Deleting duplicate entries in Excel list | Excel Discussion (Misc queries) | |||
Multiple Files, Duplicate Entries | Excel Discussion (Misc queries) |