Comparing arrays, and comparing columns
Posted by Ribbard on September 20, 2001 1:07 AM
I have several 20x20 arrays with different permutations in several worksheets.
They look something like this:
xx x xxxx
x xx x xx
xx x xxx
xx x xxx
etc. I use 'x' to indicate a condition.
I need to do the following:
1. Compare the arrays between worksheets, I just need to know if they're identical or not.
2. Compare columns within a worksheet - again just to know if there are identical columns.
Many thanks for any help!
Posted by anno on September 23, 2001 7:16 PM
ribbard
you may already have the answer, but here's one way. it's a bit ugly but should work.
**for comparing conditions between worksheets**
assuming conditions to be compared are in column A of worksheets named Sheet1 and Sheet2, enter in Sheet1:
=IF(EXACT(Sheet2!A1,A1),1)
and copy down as far as needed. with this formula you can do a SUM at the end if you want to tell you how many matches you have.
**for comparing conditions within a worksheet** assuming conditions to be compared are in columns A and B:
=IF(EXACT(A1,B1),1)
as with the other one you can do a SUM at the end.
hope this helps
Posted by anno on September 23, 2001 7:38 PM
ribbard
i should think before i write!
what i've suggested below will work for arrays as well(which is what i think you were asking for in the first place - i'm a bit slow today) but of course you'll have to array enter (ctrl+shift+enter) the formula. for columns you'll have to compare row by row, unless you define names for the columns (insert menu, name,define) then you can use =EXACT(range1,range2) and array enter.
Posted by Ribbard on September 24, 2001 5:05 AM
Anno, many thanks for your help on this (NT)
