Excel help ....

[patron]

Dear all
First of all, happy new year, and best wishes for you and your families..

I'm trying to make an excel macro.
Let's say that i have my data in excel spreedsheet (O4:Y403)
the data above is 400 sets of combinations for Greek lotto 649, that was created from "LottoSelectorXLp" Respect to nick koutras.
What i was trying to do is to construct a excel macro to identify and count the unique combinations.
Each set of combination has 11 numbers.

If you have an idea, please reply

 

[GillesD]

Excel help

May be I could help you but I do not really understand your need.

Out of your 400 lines with each 11 numbers, between 1 and 49, I assume, what do you mean by "... identify and count the unique combinations..."?

Basically, for each line of 11 numbers, you can make 462 6-numbers different combinations. So with 400 lines, you could come up with 184,800 combinations. There may be some duplicates but I doubt somebody could come up with the number of unique combinations, unless the individual of numbers on each line are known or at least the method to generate them.

Give a pratical example of what you want and then may be ...

 

[patron]

Dear GillesD

thank you for your reply.
Let's say my data is:
1 2 3 4 5 6 7 8 9 10 11
1 2 3 4 5 6 7 8 9 10 11
5 6 7 8 9 10 11 12 13 14 15
1 2 3 4 5 6 7 8 9 10 11
3 4 5 6 7 8 9 10 11 12 13
5 6 7 8 9 10 11 12 13 14 15
e.t.c. (400 lines, and each line 11 cells)

Basically i want this macro to find how many diferent combinations i have and count them..

Thanks in advance.

 

[GillesD]

Number of unique sets of data

A simple and effective way although not the only way to achieve what you want could be:

1 - In cell Z4, enter the formula: =O4&P4&Q4&R4&S4&T4&U4&W4&X4&Y4 to generate a string with the eleven numbers and copy it down to line 403.

2 - Then sort the data based on values in column Z.

3 - In cell AA4, enter the formula: =SI(Z4=Z3,0,1) to get a 0 if values are identical and a 1 if not and copy it down to line 403.

4 - The sum of cells AA4 to AA403 will give you the number of unique sets of numbers (those rows with a 1 in column AA).

Not a very elegant way of doing it but it works if the numbers from column O to Y are in increasing order like in your example, otherwise, it is back to the drawing board.

 

[GillesD]

More elegant solution

My previous post provided a solution that identified lines with duplicate data; this could allow you to remove them or do whatever you wanted. But if you only want to know the number of unique sets of numbers, then a simpler soultion could be:

1 - As before, in cell Z4, enter the formula: =O4&P4&Q4&R4&S4&T4&U4&W4&X4&Y4 to generate a string with the eleven numbers and copy it down to line 403. Give the cells Z4:Z403 a name like Data.

2 - In the cell where you want the number of unique sets of numbers to appear, enter the formula: =SUM(1/COUNTIF(Data;Data)) but do not press Enter; instead press Control+Shift+Enter to enter an array formula which will give you the number of unique sets of numbers.

 

[patron]

Another Question...

I'm trying to make a macro to find how many times a given set of 6 numbers repeated at the previous draws.
I was thinking to use countif but it does not work.
i just want to find out how many times does the 6 numbers appear, i does not matter to know the combination
0 hit 1700
1 hit 300
2 hit 100
3 hit 15
4 hit 1
5 hit 0
6 hit 0
something like the above.
any ideas????
thank you in advance..