|
Please help....
Hi , Please if someone can help me with this… I have a worksheet 1500 lines with 17 random numbers, 1 to 70. How I can find 9 or 10 numbers from 17 that are keep repeating over and over again ... Here is the example 1 19 24 28 29 34 36 37 43 46 47 50 54 55 59 63 70 3 7 9 11 16 20 22 23 26 32 37 39 49 52 55 66 67 2 4 16 21 24 26 28 29 34 38 39 41 49 57 64 67 70 7 8 9 10 14 16 21 23 25 36 43 50 51 55 61 65 70 12 14 18 21 30 36 37 43 44 47 48 49 51 52 54 55 64 8 12 13 19 23 25 27 31 32 33 36 40 44 46 60 62 64 Thanks in advance Laly -- laly ------------------------------------------------------------------------ laly's Profile: http://www.excelforum.com/member.php...o&userid=27303 View this thread: http://www.excelforum.com/showthread...hreadid=468056 |
laly Wrote: Hi , Please if someone can help me with this… I have a worksheet 1500 lines with 17 random numbers, 1 to 70. How I can find 9 or 10 numbers from 17 that are keep repeating over and over again ... Here is the example 1 19 24 28 29 34 36 37 43 46 47 50 54 55 59 63 70 3 7 9 11 16 20 22 23 26 32 37 39 49 52 55 66 67 2 4 16 21 24 26 28 29 34 38 39 41 49 57 64 67 70 7 8 9 10 14 16 21 23 25 36 43 50 51 55 61 65 70 12 14 18 21 30 36 37 43 44 47 48 49 51 52 54 55 64 8 12 13 19 23 25 27 31 32 33 36 40 44 46 60 62 64 Thanks in advance Laly Hi Laly Assuming your data to be in Columns A - Q, in column S put the numbers from 1 - 70 In column T use this formula =COUNTIF($A$1:$Q$1500,S1), drag down, you will now have a count of how many times each number is used -- Paul Sheppard ------------------------------------------------------------------------ Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783 View this thread: http://www.excelforum.com/showthread...hreadid=468056 |
Thanks, Paul This formula helps , but I want to know if in Columns A1 to Q1, I have this numbers 19 24 28 29 43 46 47 50 63 70 I like to know if the same combination (all) numbers repeats Let say in A400 TO Q400..... -- laly ------------------------------------------------------------------------ laly's Profile: http://www.excelforum.com/member.php...o&userid=27303 View this thread: http://www.excelforum.com/showthread...hreadid=468056 |
If this combination repeat more that once and how many times .....??? -- laly ------------------------------------------------------------------------ laly's Profile: http://www.excelforum.com/member.php...o&userid=27303 View this thread: http://www.excelforum.com/showthread...hreadid=468056 |
Hi!
I have this numbers 19 24 28 29 43 46 47 50 63 70 I like to know if the same combination (all) numbers repeats Put the above numbers in a range, say, T1:AC1 Then, in R1 enter this formula: =SUMPRODUCT(--(ISNUMBER(MATCH(T$1:AC$1,A1:Q1,0)))) Copy down as needed. In column R all formulas that return 10 mean that all 10 of the number combinations are in that row. Then to get the total: =COUNTIF(R:R,10) Biff "laly" wrote in message ... Hi , Please if someone can help me with this. I have a worksheet 1500 lines with 17 random numbers, 1 to 70. How I can find 9 or 10 numbers from 17 that are keep repeating over and over again ... Here is the example 1 19 24 28 29 34 36 37 43 46 47 50 54 55 59 63 70 3 7 9 11 16 20 22 23 26 32 37 39 49 52 55 66 67 2 4 16 21 24 26 28 29 34 38 39 41 49 57 64 67 70 7 8 9 10 14 16 21 23 25 36 43 50 51 55 61 65 70 12 14 18 21 30 36 37 43 44 47 48 49 51 52 54 55 64 8 12 13 19 23 25 27 31 32 33 36 40 44 46 60 62 64 Thanks in advance Laly -- laly ------------------------------------------------------------------------ laly's Profile: http://www.excelforum.com/member.php...o&userid=27303 View this thread: http://www.excelforum.com/showthread...hreadid=468056 |
how this formula works??? =INDEX(LIST,MATCH(MAX(COUNTIF(LIST,LIST)),COUNTIF( LIST,LIST),0)) -- laly ------------------------------------------------------------------------ laly's Profile: http://www.excelforum.com/member.php...o&userid=27303 View this thread: http://www.excelforum.com/showthread...hreadid=468056 |
Let's assume that A1:A5 contains the following values...
D B A C A ....and that we have the following formula... =INDEX(A1:A5,MATCH(MAX(COUNTIF(A1:A5,A1:A5)),COUNT IF(A1:A5,A1:A5),0)) ....here's how it breaks down... COUNTIF(A1:A5,A1:A5) returns the following array of values... 1 1 2 1 2 Hence, we have... =INDEX({"D";"B";"A";"C";"A"},MATCH(MAX({1;1;2;1;2} ),{1;1;2;1;2},0)) ....which gives us... =INDEX({"D";"B";"A";"C";"A"},MATCH(2,{1;1;2;1;2},0 )) ....then... =INDEX({"D";"B";"A";"C";"A"},3) ....and returns 'A'. Hope this helps! In article , laly wrote: how this formula works??? =INDEX(LIST,MATCH(MAX(COUNTIF(LIST,LIST)),COUNTIF( LIST,LIST),0)) |
Hi, Let say i have a woksheet with 17 random numbers 1 to 70 in (A1-Q1) i like to know what combination of 7 numbers from 17 ...are the most repeat in A1-A1500 I dont know the numbers ,I want to know which numbers (COMBINATION OF 7 ) -- laly ------------------------------------------------------------------------ laly's Profile: http://www.excelforum.com/member.php...o&userid=27303 View this thread: http://www.excelforum.com/showthread...hreadid=468056 |
If I understand your question, I don't think it's possible.
Combinations of 7 from a group of 70 = 1,198,774,720 possibilities. Biff "laly" wrote in message ... Hi, Let say i have a woksheet with 17 random numbers 1 to 70 in (A1-Q1) i like to know what combination of 7 numbers from 17 ...are the most repeat in A1-A1500 I dont know the numbers ,I want to know which numbers (COMBINATION OF 7 ) -- laly ------------------------------------------------------------------------ laly's Profile: http://www.excelforum.com/member.php...o&userid=27303 View this thread: http://www.excelforum.com/showthread...hreadid=468056 |
Hi Biff it Combinations of 7 from a group of 17 not 70 7 NUMBERS 1:61,640 6 NUMBERS 1:1,828 A1 TO Q1 1 19 24 28 29 34 36 37 43 46 47 50 54 55 59 63 70 3 7 9 11 16 20 22 23 26 32 37 39 49 52 55 66 67 2 4 16 21 24 26 28 29 34 38 39 41 49 57 64 67 70 7 8 9 10 14 16 21 23 25 36 43 50 51 55 61 65 70 12 14 18 21 30 36 37 43 44 47 48 49 51 52 54 55 64 8 12 13 19 23 25 27 31 32 33 36 40 44 46 60 62 64 -- laly ------------------------------------------------------------------------ laly's Profile: http://www.excelforum.com/member.php...o&userid=27303 View this thread: http://www.excelforum.com/showthread...hreadid=468056 |
Hi!
it Combinations of 7 from a group of 17 not 70 What does this mean, then: Let say i have a woksheet with 17 random numbers 1 to 70 in (A1-Q1) This is not possible to do on a desktop computer! Domenic is good, but even he is not that good! <g Biff "laly" wrote in message ... Hi Biff it Combinations of 7 from a group of 17 not 70 7 NUMBERS 1:61,640 6 NUMBERS 1:1,828 A1 TO Q1 1 19 24 28 29 34 36 37 43 46 47 50 54 55 59 63 70 3 7 9 11 16 20 22 23 26 32 37 39 49 52 55 66 67 2 4 16 21 24 26 28 29 34 38 39 41 49 57 64 67 70 7 8 9 10 14 16 21 23 25 36 43 50 51 55 61 65 70 12 14 18 21 30 36 37 43 44 47 48 49 51 52 54 55 64 8 12 13 19 23 25 27 31 32 33 36 40 44 46 60 62 64 -- laly ------------------------------------------------------------------------ laly's Profile: http://www.excelforum.com/member.php...o&userid=27303 View this thread: http://www.excelforum.com/showthread...hreadid=468056 |
In A1 i have 17 numbers A1=12 15 17 11 3 5 33 66 70 28 18 19 4 5 10 46 67 how can i put this numbers in B1=12 C1=15 D1=17 E1=11................... thanks.... -- laly ------------------------------------------------------------------------ laly's Profile: http://www.excelforum.com/member.php...o&userid=27303 View this thread: http://www.excelforum.com/showthread...hreadid=468056 |
Select A1
data|text to columns (on the menubar) delimited by space finish up. laly wrote: In A1 i have 17 numbers A1=12 15 17 11 3 5 33 66 70 28 18 19 4 5 10 46 67 how can i put this numbers in B1=12 C1=15 D1=17 E1=11................... thanks.... -- laly ------------------------------------------------------------------------ laly's Profile: http://www.excelforum.com/member.php...o&userid=27303 View this thread: http://www.excelforum.com/showthread...hreadid=468056 -- Dave Peterson |
| All times are GMT +1. The time now is 05:51 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com