Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
excel 2002
I'm trying to use the =OR(Exact) & compare a range of cells. Having
problems. It seems to work when the cells are in the same row, but not when comparing to cells in different rows in the range. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
excel 2002
Pl. share the formula you are using...
Exact returns TRUE or FALSE... Why are you wrapping it in OR? "markholt" wrote: I'm trying to use the =OR(Exact) & compare a range of cells. Having problems. It seems to work when the cells are in the same row, but not when comparing to cells in different rows in the range. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
excel 2002
The following array formula will return the number of EXACT matches in
the range A1:A6 compared, row by row, with B1:B6. =SUM(1*(EXACT(A1:A6,B1:B6))) The following array formula will return TRUE if all cells in A1:A6 are exact matches, row by row, with the values in B1:B6. It will return FALSE if one or more pair of cells are not an exact match. =SUM(1*EXACT(A1:A6,B1:B6))=ROWS(A1:A6) EXACT performs a case-sensitive match. E.g., "A" is not equal to "a". These are both array formulas, so you must press CTRL SHIFT ENTER rather than just ENTER when you first enter the formula 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 must 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 Sun, 26 Oct 2008 07:14:01 -0700, markholt wrote: I'm trying to use the =OR(Exact) & compare a range of cells. Having problems. It seems to work when the cells are in the same row, but not when comparing to cells in different rows in the range. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
excel 2002
Hi,
We need more information - are you trying to compare every cell in one range with every one in another range and determine if or how many are exact matches. Note this is different than comparing cell by cel. For example A1 B1 A2 B2 A3 B3 Do you want to check if A1 is in any of the cells B1:B3 or just compare it with B1? If you wanted to check A1 against all the cells in B1:B3 it would be =OR(EXACT(A1,B1:B3)) entered as an array (Shift+Ctrl+Enter) If you wanted to compare A1 to B1, A2 to B2 and determine how many were exact matches the formula would be =SUMPRODUCT(--EXACT(A1:A3,B1:B3)) If you are trying to find out how many items in column A are also in column B regardless of position then =SUMPRODUCT(COUNTIF(A1:A3,B1:B3)) But this is not a case sensitive count. -- Thanks, Shane Devenshire "markholt" wrote: I'm trying to use the =OR(Exact) & compare a range of cells. Having problems. It seems to work when the cells are in the same row, but not when comparing to cells in different rows in the range. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Open Excel 2002 Attachment from Outlook 2002 | Excel Discussion (Misc queries) | |||
Excel 2002: How is the Excel 2007 file format open in Excel 2002 ? | Excel Discussion (Misc queries) | |||
Excel 2002 : Unable to open files in MS Outlook 2002 | Excel Discussion (Misc queries) | |||
Outlook 2002 calendar dates exported to Excel 2002 sort incorrectl | Excel Worksheet Functions | |||
Can you print labels using Excel 2002 in a Word 2002 mail merge? | Excel Discussion (Misc queries) |