Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Two arrays same?
What formula could tell A1:J1 = A10:J10?
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Two arrays same?
Try one of these:
=SUMPRODUCT(--(A1:J1=A10:J10))=10 Or, array entered** : =AND(A1:J1=A10:J10) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "FARAZ QURESHI" wrote in message ... What formula could tell A1:J1 = A10:J10? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Two arrays same?
Hello,
Biff already provided two solutions. But if you need to be a little bit more tolerant with regards to text & numbers, for example if the text 2 should be treated as being identical to the number 2 then use =SUMPRODUCT(--(EXACT(A1:J1,A10:J10)))=10 Regards, Bernd |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Two arrays same?
I would write the SUMPRODUCT formula this way...
=SUMPRODUCT(--(A1:J1<A10:J10))=0 That way, you will not have to remember to adjust the 10 to reflect the number of cells in a range (in case this formula gets used elsewhere on ranges containing different numbers of cells). -- Rick (MVP - Excel) "T. Valko" wrote in message ... Try one of these: =SUMPRODUCT(--(A1:J1=A10:J10))=10 Or, array entered** : =AND(A1:J1=A10:J10) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "FARAZ QURESHI" wrote in message ... What formula could tell A1:J1 = A10:J10? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Two arrays same?
You could do this and still maintain the intuitive logic (are the cells
*equal*): =SUMPRODUCT(--(A1:J1=A10:J10))=COLUMNS(A1:J1) My preference would be the array formula. -- Biff Microsoft Excel MVP "Rick Rothstein" wrote in message ... I would write the SUMPRODUCT formula this way... =SUMPRODUCT(--(A1:J1<A10:J10))=0 That way, you will not have to remember to adjust the 10 to reflect the number of cells in a range (in case this formula gets used elsewhere on ranges containing different numbers of cells). -- Rick (MVP - Excel) "T. Valko" wrote in message ... Try one of these: =SUMPRODUCT(--(A1:J1=A10:J10))=10 Or, array entered** : =AND(A1:J1=A10:J10) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "FARAZ QURESHI" wrote in message ... What formula could tell A1:J1 = A10:J10? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Arrays | Excel Worksheet Functions | |||
Regarding Arrays | Excel Discussion (Misc queries) | |||
Regarding the Arrays | Excel Discussion (Misc queries) | |||
Arrays | Excel Discussion (Misc queries) | |||
Need help with arrays (I think) | Excel Worksheet Functions |