Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting pairs | Excel Worksheet Functions | |||
Working with pairs of cells | Excel Worksheet Functions | |||
Sum product of many pairs of columns | Excel Worksheet Functions | |||
Labeling Data Pairs | Charts and Charting in Excel | |||
Numbering data pairs | Charts and Charting in Excel |