Problem---

[Sopranos]

I have numbers in 7 columns (A-G) and 499 rows (1-499).
-Question 1: How could I see if exist two or more same rows?For example:


A B C D E
F G
3 4 19 21 25 32 38
2 3 20 22 24 25 38
6 12 13 23 28 38 39
3 6 7 17 32 37 38
5 14 23 27 29 31 37
3 4 19 21 25 32 38
5 6 11 20 22 24 34
2 5 18 22 23 25 37
2 7 13 18 25 31 32
5 13 17 28 32 36 39


How can I get how manny combinations of 3 4 19 21 25 32 38

is there in whole table?


-Question 2: How could I see how manny combinations with 5 same numbers and
two diferent there is? For example:

A B C D E
F G
3 4 19 21 25 32 38
2 3 20 22 24 25 38
6 12 13 23 28 38 39
3 6 7 17 32 37 38
5 14 23 27 29 31 37
3 4 19 21 24 27 38
5 6 11 20 22 24 34
2 5 18 22 23 25 37
2 7 13 18 25 31 32
5 13 17 28 32 36 39


How can i see that there is 2 combinations where exist 5 same numbers and 2
diferrent?

3 4 19 21 25 32 38


3 4 19 21 24 27 38



Sorry for my english... I hope that you understand me... Thanks!

 

[T. Valko]

Try these:

Both are array formulas. They need to be entered using the key combination
of CTRL,SHIFT,ENTER (not just ENTER):

The table of numbers is in the range A1:G499

The set of 7 numbers to lookup is in the range I1:O1 -

3 4 19 21 25 32 38

For all 7 numbers:

=SUMPRODUCT(--(MMULT(--ISNUMBER(MATCH(A1:G499,I1:O1,0)),TRANSPOSE(COLUMN(A1:G499)^0))=7))

For 5 out of 7:

=SUMPRODUCT(--(MMULT(--ISNUMBER(MATCH(A1:G499,I1:O1,0)),TRANSPOSE(COLUMN(A1:G499)^0))=5))

Note: if a set has 6 or 7 numbers that match it will not be counted as a 5
out 7 match.

Biff

 

[dribler2]

T. Valko,
your formula works well to see the rows having match values on per each
cells, but it also considers matching if one row consist of 7 cells with one
match number
e.g.
scenario of one matched row
3 4 19 21 25 32 38
3 3 3 3 3 3 3
this is considered as a one full 7 - match..
maybe i am confused...or maybe u can expand in a more distinct way..
happy holidays...
dribler2

 

[T. Valko]

Yes, I see what you mean, however, I don't think that's a concern based on
the "characteristics" of the sample data. Looks like these may be lottery
numbers.

Biff