Help Comparing Arrays (to Other Arrays)

Hi Excel Forum,

Cross-post here: http://www.mrexcel.com/forum/excel-q...er-arrays.html

Here is an illustation of my real data:

{1	1	1	1}
{1	9	1	1}
{1	1	9	9}

Here is the problem:
All values in {1,1,1,1} are < or = to all corresponding values in at least one other row in this example (both the second and third rows). In other words, {1,1,1,1} is obsolete because there is at least one other row that meets or exceeds all corresponding values. On the contrary, neither {1,9,1,1} nor {1,1,9,9} are obsolete; no row, excluding themselves, can utterly beat {1,9,1,1} nor {1,1,9,9} in a one-on-one fight.

We need to compare each row to every other row in our big data. In the process, the obsolete rows are marked. The end result may be condition formatting to the data or a separate column of Y's/N's, TRUE's/FALSE's, etc. (denoting obsolete).

Here is what I tried:
=--(F17:L17<=F18:L18)={1,1,1,1,1,1,1} We compare a row in question (F17:L17) to one other row (F18:L18) by <=. Formula produces an array of TRUE's and FALSE's, and the "--" converts that to an array of 1's and 0's. Result after CTRL+SHIFT+Enter is a single cell with TRUE (expected). The main disadvantage of this method is we would end up with large tables and a lot of manual work. We are are not sure our method works and would like something cleaner. Also note we want to exclude comparing a given row with itself.




This is my first post on the forum. I am not an expert at Excel (little experience with formulas, macros, or scripting), so feel free to elaborate. Thank you in advance!

- Polite Master

welcome to the forum. not sure if i got your question right. you also explained your real data in 4 columns while your formula eg shows F17:L17 (7 columns). i'm just going to assume data in F17:I19 like the way you illustrated your data in 4 columns & 3 rows. then try this in say J17:
=SUMPRODUCT(--(F17:I17<=$F$17:$I$19))=COUNT($F$17:$I$19)

it will turn up as TRUE because all other rows of the same column have values <= to it. copy down & the other 2 rows will turn up as FALSE. if i got it wrong, here's how i normally recommend people ask their questions:
http://www.excelforum.com/tips-and-t...d-answers.html

Okay, here is the attached workbook

P.S. The cell references above do not correspond with uploaded data, which now starts at A2.
Reuploaded 2x for typos.

still not sure if i understood correctly. try this in I2:

Formula:

Please Login or Register  to view this content.

if i got it wrong, tell me the row & why it's wrong

glad to help. =)

COUNTIF can be used with different operators like:
= equals
> greater than
>= greater than or equals to
etc...
by not putting any operators, the default is "equals". so if i wanna count how many times "10" appears in A1:A10, it'll be:
=COUNTIF(A1:A10,"10")
you can also put it as:
=COUNTIF(A1:A10,"=10")
*for numbers, it can be without the double quotes. but i just needed to illustrated the it for the next part
=COUNTIF(A1:A10,10)

to count how many cells are greater or equals to 10, you need to put in:
=COUNTIF(A1:A10,">=10")

if 10 is in a separate cell in say C1, then you have to combine the operator with the cell reference. so:
=COUNTIF(A1:A10,">="&C1)

so basically in your case of row 2, i need to count how many cells in $A$2:$A$72 is greater than or equals to A2, AND how many in $B$2:$B$72 is greater than or equals to B2, etc...

at least 1 row will be, because row 2 itself will be equals to row 2. so only when the count is equals to 1, it's obsolete. hope that helps