finding pairs

  • Thread starter Thread starter Len Case
  • Start date Start date
L

Len Case

Hi all....could someone help with this please,
I have an array about 50 long and 6 cols wide filled with numbers
of which 45 is the highest. What I need is to know how many pairs of 2s and
3s there are
in each row if any, or 2s and 4s in each row or 2s and 5s in each row
please.
Thankyou
Len
 
Can you post some samples and the expected results?

Do you mean 2s and 3s side by side:

1,2,3,2,3,5

Pairs anywhere:

1,2,3,3,2,5

Will there be uneven pairs:

1,2,3,2,3,2

You need to *explicitly* define what you mean.
 
Sorry about that, this is the array that I want to find pairs that are in
rows, EG row 1 = 1and 4 row 8 = 1 and 4
also I can find these pairs in row6= 2and 9 and the last row 2 and 9, in
fact I want to find all pairs in all the rows.
Thanks for your help
Len

1
10
4
23
33
39
1 3 16 22 28 29
4 13 14 21 24 29
5 6 7 10 14 31
8 19 21 23 32 43
2 9 20 33 36 39
5 11 25 28 30 34
1 4 8 22 34 44
7 13 23 33 37 43
8 13 16 23 30 44
5 14 19 22 41 44
16 18 28 32 37 39
3 15 22 35 37 41
11 17 28 30 41 43
9 10 22 27 32 34
1 2 6 9 26 42
 
Ok, it looks like any number can only appear once per line. Are these
lottery numbers?

To count how many times 1 and 4 appear in the same row:

=SUMPRODUCT(--(MMULT(--ISNUMBER(MATCH(A1:F16,{1,4},0)),{1;1;1;1;1;1})=2))

Note that this is limited to a maximum of 5460 rows. If you have more rows
than that a different approach will be needed.
 
Thankyou very much that is what I was looking for, how the heck do you find
and work out formulas like that.
thanks again and by the way if I win the lottery as you guessed I will send
you a few bob.
cheers
Len
 
Back
Top