Please help....

[laly]

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 an
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

Lal

 

[Paul Sheppard]

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

 

[laly]

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]

If this combination repeat more that once and how many times .....???

 

[Biff]

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]

how this formula works???


=INDEX(LIST,MATCH(MAX(COUNTIF(LIST,LIST)),COUNTIF(LIST,LIST),0))

 

[Domenic]

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)),COUNTIF(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!

 

[laly]

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 mos
repeat in A1-A1500
I dont know the numbers ,I want to know which numbers (COMBINATION O
7

 

[Biff]

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]

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 6

 

[Biff]

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]

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=1
E1=11...................

thanks...

 

[Dave Peterson]

Select A1
data|text to columns (on the menubar)
delimited
by space
finish up.