| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Detect Duplicates
I have a list of values in B11 down to B97. The values are either numbers or
text, but there are no empty cells in the list. Is there a formula that will give me 0 if all the values are unique and give me 1 if any of the values are duplicated? Thanks in advance for any help. -- jake |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Detect Duplicates
yup...arrange the column is ascending/descending order..
then in column C, apply this formula =B11=B12..... Drag this to the entire column.. if two subsequent rows are the same, it returns -true, else if all are unique, it would return false "Jakobshavn Isbrae" wrote: I have a list of values in B11 down to B97. The values are either numbers or text, but there are no empty cells in the list. Is there a formula that will give me 0 if all the values are unique and give me 1 if any of the values are duplicated? Thanks in advance for any help. -- jake |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Detect Duplicates
Try this, array-entered (press CTRL+SHIFT+ENTER to confirm the formula):
=IF(MAX(COUNTIF(B11:B97,B11:B97))1,1,0) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Jakobshavn Isbrae" wrote: I have a list of values in B11 down to B97. The values are either numbers or text, but there are no empty cells in the list. Is there a formula that will give me 0 if all the values are unique and give me 1 if any of the values are duplicated? Thanks in advance for any help. -- jake |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Detect Duplicates
This is a great resource that may give you more ideas about working with
dupes in Excel: http://www.cpearson.com/excel/Duplicates.aspx Regards, Ryan--- -- RyGuy "Max" wrote: Try this, array-entered (press CTRL+SHIFT+ENTER to confirm the formula): =IF(MAX(COUNTIF(B11:B97,B11:B97))1,1,0) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Jakobshavn Isbrae" wrote: I have a list of values in B11 down to B97. The values are either numbers or text, but there are no empty cells in the list. Is there a formula that will give me 0 if all the values are unique and give me 1 if any of the values are duplicated? Thanks in advance for any help. -- jake |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Detect Duplicates
thank you
-- jake "Max" wrote: Try this, array-entered (press CTRL+SHIFT+ENTER to confirm the formula): =IF(MAX(COUNTIF(B11:B97,B11:B97))1,1,0) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Jakobshavn Isbrae" wrote: I have a list of values in B11 down to B97. The values are either numbers or text, but there are no empty cells in the list. Is there a formula that will give me 0 if all the values are unique and give me 1 if any of the values are duplicated? Thanks in advance for any help. -- jake |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Detect Duplicates
Welcome, Jake
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Jakobshavn Isbrae" wrote in message ... thank you -- jake |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to detect characters with a cell? | Excel Discussion (Misc queries) | |||
| How to detect #N/A and return 0? | Excel Discussion (Misc queries) | |||
| detect cells with links | Excel Worksheet Functions | |||
| How to detect changes in a column? | Excel Discussion (Misc queries) | |||
| how to detect 5th character? | Excel Discussion (Misc queries) | |||
Linear Mode
