Array formula question

[nisaf]

Have a question ---

I have a database 9 columns 236 rows.
------ would like to do an iterative data comparison
i.e. compare all 9 cells in row 1 and if they are <= cells in row 2, then return a 1 (true) or 0 (false), then compare row1 to row3, row1 to row4 etc until we have done comparison of row1 to row236.
Then do the same thing for row2 --- cf. row2 to row3, row2 to row 4 ....
Row2 to row236. etc etc.

Any ideas on how to do this efficiently.
Thanks in advance

 

[GlennUK]

So, for row 236 you would have 235 results of 1 or 0? Is that right?

 

[sbendbuckeye]

Do you only want a global True and False, in other words as soon as one is different stop processing or do you literally want to check each one? Good Luck!

 

[nisaf]

Hi GlennUK
-- the way I figure it, overall there will be 27,730 comparisons
i.e. for row1 there will be 235 comparisons(r1 to r2, r1 to r3, ... r1 to r236); for row2 there are 234 comparisons (r2 to r3, r2 to r4.. r2 to r236) etc until row235 where there is 1 comparison(r235 to r236).

 

[pgc01]

Hi nisaf

You are right. There are COMBIN(236,2)=27730 comparisons.

One suggestion. Write the row combinations and use them with INDEX to locate the cells to compare.

Hope this helps
PGC

 

[GlennUK]

I would write a macro to generate the combinations in 27730 pairs of cells, and write a formula to do the comparison that could be copied down the list. Is that the kind of solution that would suit?