| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Check no cell is the same within a range
Dear all,
The range A1:A500 contains references. What formula would I require to return a true of false answer to the question: are all cells different? i.e. are all references independent. Thanking-you in advance. Kind regards, Neil |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Check no cell is the same within a range
In any available empty column, put this formula in a cell on row 1 and fill
it down to row 500: =IF(COUNTIF($A$1:$A$500,A1)=1,"","MULTIPLE ENTRIES") Rather than nag you with a bunch of "TRUE" entries when the item on a row only appears once, this leaves it blank, making the MULTIPLE ENTRIES noted stand out better. If you want a real TRUE/FALSE output, then use =COUNTIF($A$1:$A$10,A1)=1 Either case, when you have an entry that is repeated, each instance of it will be marked as either "MULTIPLE ENTRIES" (first formula) or FALSE (2nd formula). Hope this helps. "Neil Pearce" wrote: Dear all, The range A1:A500 contains references. What formula would I require to return a true of false answer to the question: are all cells different? i.e. are all references independent. Thanking-you in advance. Kind regards, Neil |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Check no cell is the same within a range
The following array formula will return TRUE if all items in A1:A10
are distinct (no duplicates, ignoring empty cells) or FALSE if there are duplicate values in A1:A10. =MAX(COUNTIF(A1:A10,A1:A10))<=1 This is an array formula, so you must press CTRL SHIFT ENTER rather than just ENTER when you first enter the cell and whenever you edit it later. If you do this properly, Excel will display the formula enclosed in curly braces { }. See http://www.cpearson.com/Excel/ArrayFormulas.aspx for more information about array formulas. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Thu, 23 Oct 2008 05:05:01 -0700, Neil Pearce wrote: Dear all, The range A1:A500 contains references. What formula would I require to return a true of false answer to the question: are all cells different? i.e. are all references independent. Thanking-you in advance. Kind regards, Neil |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Check no cell is the same within a range
An alternative to JLatham is to use the single cell array formula:
=IF(MAX(COUNTIF(A1:A500,A1:A500))1,"not all are different","all are different") The formula must be entered with CNTRL-SHFT-ENTER rather than just the ENTER key. -- Gary''s Student - gsnu200809 "Neil Pearce" wrote: Dear all, The range A1:A500 contains references. What formula would I require to return a true of false answer to the question: are all cells different? i.e. are all references independent. Thanking-you in advance. Kind regards, Neil |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Check the interval of a cell withni a range of values | Excel Discussion (Misc queries) | |||
| Check if text exists within a cell range and return logical vaule - possible? | Excel Discussion (Misc queries) | |||
| Check if rounded cell values fall within a range | Excel Discussion (Misc queries) | |||
| Check if a cell contains text compared to a range of cells | Excel Discussion (Misc queries) | |||
| Check to see if cell data is within a range | Excel Worksheet Functions | |||
Hybrid Mode
