A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

array formula to check if ranges are identical



 
 
Thread Tools Display Modes
  #1  
Old April 23rd, 2004, 11:54 PM
Harlan Grove
external usenet poster
 
Posts: n/a
Default 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  
Old April 26th, 2004, 09:53 PM
upstate_steve
external usenet poster
 
Posts: n/a
Default 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  
Old April 27th, 2004, 05:58 AM
Harlan Grove
external usenet poster
 
Posts: n/a
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:57 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.