finding pairs
 

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

finding pairs
 

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 anywhe

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.

--
Biff
Microsoft Excel MVP


"Len Case" wrote in message
...
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

finding pairs
 

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











"T. Valko" wrote in message
...
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 anywhe

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.

--
Biff
Microsoft Excel MVP


"Len Case" wrote in message
...
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

finding pairs
 

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.

--
Biff
Microsoft Excel MVP


"Len Case" wrote in message
...





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











"T. Valko" wrote in message
...
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 anywhe

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.

--
Biff
Microsoft Excel MVP


"Len Case" wrote in message
...
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

finding pairs
 

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




"T. Valko" wrote in message
...
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.

--
Biff
Microsoft Excel MVP


"Len Case" wrote in message
...





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











"T. Valko" wrote in message
...
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 anywhe

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.

--
Biff
Microsoft Excel MVP


"Len Case" wrote in message
...
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

finding pairs
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Len Case" wrote in message
...
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




"T. Valko" wrote in message
...
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.

--
Biff
Microsoft Excel MVP


"Len Case" wrote in message
...





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











"T. Valko" wrote in message
...
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 anywhe

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.

--
Biff
Microsoft Excel MVP


"Len Case" wrote in message
...
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