If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
array formula to check if ranges are identical
"upstate_steve " wrote...
Is there an array formula that will determine if the data in two multi-column ranges are identical? Identical just in terms of contents, e.g., {1;2;3} would be identical to {3;1;2}, or indentical in terms of both contents and location? If the latter, there's always the array formula =AND(RngA=RngB) If the former, the array formula =AND((COUNTIF(RngA,""&RngA)=COUNTIF(RngB,""&RngA )) *(COUNTIF(RngA,""&RngB)=COUNTIF(RngB,""&RngB)) *COUNTIF(RngA,RngB)*COUNTIF(RngB,RngA)) Also, how would I return an array of the row numbers of the rows in Range A that are not identical to the corresponding row in Range B? =SMALL(IF(RngARngB,ROW(RngA)),ROW(INDIRECT("1:"& SUMPRODUCT(--(RngARngB))))) I'm interested in a single array formula that will accomplish this, not adding columns of formulas next to one of the ranges. Such single array formulas would have variable sizes depending on what your ranges exactly contain. You'd need to wrap them inside IF constructs or live with error values in some cells. -- To top-post is human, to bottom-post and snip is sublime. |
#2
|
|||
|
|||
array formula to check if ranges are identical
Harlan Grove:
Thanks. I'm still trying to tease out the internal logic of the formulas, but they definitely work. One more thing. How about a formula that compares rows? That is to say, "true" if RngB contains those rows--and only those rows--present in RngA (regardless of sort order), but false otherwise, even if RngB contains all those values--and only those values--present in RngA. Thanks Steve --- Message posted from http://www.ExcelForum.com/ |
#3
|
|||
|
|||
array formula to check if ranges are identical
"upstate_steve " wrote ...
.... How about a formula that compares rows? That is to say, "true" if RngB contains those rows--and only those rows--present in RngA (regardless of sort order), but false otherwise, even if RngB contains all those values--and only those values--present in RngA. I think you mean check if RngB spans exactly the same rows as RngA, so =AND(ROWS(RngA)=ROWS(RngB),CELL("Row",RngA)=CELL(" Row",RngB)) |
Thread Tools | |
Display Modes | |
|
|