how do I create a macro to sort out data pairs in excel?

Question

Let´s say I have a long list of lottery numbers in different dates and I want to set aside only prime numbers out of it or calculate how many times the number 45 appears. I asked this question before but I guess it was poorly elaborated...

Answer 1

Your question still isn't completely clear, but the following will tell you how to calculate the frequency of each number being on the winning ticket:

Let's assume you have the dates in A1, A2 ....etc. and for each date the winning numbers are in cols. B through G (i.e. there are 7 numbers for each date).

Now select the range of cells containing all the winning numbers, say B1 through G100, and name the range "Results" by clicking on the menu Insert - Name - Define and typing "Results" in the dialog box (no quotation marks)

Lets say the lotto numbers are from 1 to 60. In cell H1 enter 1, in cell H2 enter 2. Select both cells, position the mouse cursor at the bottom right-hand corner, and when the cursor turns into a "+" click and drag down to cell H60. Now cells H1 through H60 will have the numbers 1 to 60. In cell I1 enter the following formula:

=Countif("Results",H1)

Copy cell I1 and paste into cells I2 through I60.

Cells I1 through I60 will indicate the number of days that each of the numbers have been on the winning ticket.

Answer 2

Use countif() to calculate how many times a certain number appears, use rank() or percentile function() to get the top scorers. If you are thinking of doing analysis for lotto and powerball, it won't help you win the big one but your excel will definitely improve.