Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding duplicates based on 2 columns
Greetings all,
oh hear me, practitioners of spreadsheetery, and heed my humble plea for aid... I'm struggling with finding duplicates based on the values in two columns. Basically, I've got people entering a list of IDs in one column and a list of values in another. I want to make sure they don't accidentally enter the same value for any given ID more than once. Example: ID Value 123 June 123 July 123 Feb 123 July <- duplicate 295 March 477 September 477 December The desired outcome is to either prevent them from entering a dupe via data validation, to use conditional formatting to flag dupes. thanks! ..o. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding duplicates based on 2 columns
How about data validation?
Assume the range in question is B1:B10 Select the range B1:B10 Goto the menu DataValidation Select Custom Formula: =COUNTIF(B$1:B$10,B1)<=1 You can select the type of error message that pops up by clicking on the Error Alert tab and filling in the info OK out Biff wrote in message ups.com... Greetings all, oh hear me, practitioners of spreadsheetery, and heed my humble plea for aid... I'm struggling with finding duplicates based on the values in two columns. Basically, I've got people entering a list of IDs in one column and a list of values in another. I want to make sure they don't accidentally enter the same value for any given ID more than once. Example: ID Value 123 June 123 July 123 Feb 123 July <- duplicate 295 March 477 September 477 December The desired outcome is to either prevent them from entering a dupe via data validation, to use conditional formatting to flag dupes. thanks! .o. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding duplicates based on 2 columns
Thanks for the prompt reply! Hmmm... question: won't that prevent all
duplicate entries within the B1:B10 (or whatever) range? Duplicate values in the range are fine, it's only dupes within the range _and_ each ID that are problematic. My example was poor, lemme tweak it. ID Value 123 June 123 July <- duplicate (same ID and month) 123 Feb 123 July <- duplicate (same ID and month) 295 March 295 July <- not duplicate (same month, but different ID) 477 September 477 December thanks! ..o. On Mar 27, 11:08 am, "T. Valko" wrote: How about data validation? Assume the range in question is B1:B10 Select the range B1:B10 Goto the menu DataValidation Select Custom Formula: =COUNTIF(B$1:B$10,B1)<=1 You can select the type of error message that pops up by clicking on the Error Alert tab and filling in the info OK out Biff |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding duplicates based on 2 columns
Use this formula:
=SUMPRODUCT(--(A$1:A1&B$1:B1=A1&B1))<=1 Same setup procedure Biff wrote in message ups.com... Thanks for the prompt reply! Hmmm... question: won't that prevent all duplicate entries within the B1:B10 (or whatever) range? Duplicate values in the range are fine, it's only dupes within the range _and_ each ID that are problematic. My example was poor, lemme tweak it. ID Value 123 June 123 July <- duplicate (same ID and month) 123 Feb 123 July <- duplicate (same ID and month) 295 March 295 July <- not duplicate (same month, but different ID) 477 September 477 December thanks! .o. On Mar 27, 11:08 am, "T. Valko" wrote: How about data validation? Assume the range in question is B1:B10 Select the range B1:B10 Goto the menu DataValidation Select Custom Formula: =COUNTIF(B$1:B$10,B1)<=1 You can select the type of error message that pops up by clicking on the Error Alert tab and filling in the info OK out Biff |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding duplicates based on 2 columns
Bingo. So lessee, this concatenates and compares the A+B you entered
with every other instance of A+B, counts them, and requires the total to be 1 or less. The string to number conversion is necessary because there's nothing similar to SUMPRODUCT that can be used for strings I guess? thanks! ..o. T. Valko wrote: Use this formula: =SUMPRODUCT(--(A$1:A1&B$1:B1=A1&B1))<=1 Same setup procedure Biff |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding duplicates based on 2 columns
SUMPRODUCT works with numbers. The resulting tests evaluate to either TRUE
or FALSE. The "--" coerces these TRUE or FALSE to either 1 for TRUE and 0 for FALSE. Then Sumproduct adds them up and compares that number to <=1. Biff wrote in message oups.com... Bingo. So lessee, this concatenates and compares the A+B you entered with every other instance of A+B, counts them, and requires the total to be 1 or less. The string to number conversion is necessary because there's nothing similar to SUMPRODUCT that can be used for strings I guess? thanks! .o. T. Valko wrote: Use this formula: =SUMPRODUCT(--(A$1:A1&B$1:B1=A1&B1))<=1 Same setup procedure Biff |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
help...finding duplicates | Excel Worksheet Functions | |||
Finding Duplicates | Excel Worksheet Functions | |||
Finding duplicates | Excel Worksheet Functions | |||
Finding Duplicates | Excel Worksheet Functions | |||
Finding Duplicates | Excel Worksheet Functions |