Excel Formula or Macro

[Kenya649]

I have the following table that has column 1 to 35 and row 1 to 33.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35
1 6 4 1 1 2 2 2 3 5 2 2 3 0 2 2 0 5 2 4 3 2 3 3 4 2 1 1 1 0 0 1 1 2 2 0
2 5 4 1 2 1 2 3 3 3 2 1 3 2 1 2 1 6 4 3 5 1 3 2 4 1 2 0 0 1 1 2 0 2 2 2
3 6 3 1 0 1 4 3 1 2 1 3 2 1 1 3 0 6 3 5 4 1 3 3 4 2 0 1 1 1 0 0 2 1 1 1
4 7 5 1 2 1 2 3 3 2 1 2 2 2 2 2 2 7 4 2 5 3 3 4 4 0 2 1 0 1 1 4 1 0 1 1
5 6 6 2 2 1 2 3 2 0 2 1 2 0 2 2 0 5 3 2 4 2 3 3 5 1 1 1 2 0 0 2 1 3 0 1
6 7 5 1 1 2 2 1 3 1 0 2 1 2 2 2 0 6 5 4 6 2 3 4 4 0 1 0 2 2 0 1 2 2 2 1
7 7 6 1 2 0 3 3 3 1 2 2 1 3 2 2 0 7 5 3 6 2 4 3 5 0 1 0 2 1 1 2 1 3 1 1
8 6 3 1 1 2 2 3 3 2 3 2 0 4 1 3 2 7 6 3 7 1 3 3 4 0 0 0 1 4 0 2 0 1 4 0
9 4 0 0 0 2 3 2 4 2 0 1 2 3 2 2 0 6 4 3 5 2 2 2 4 2 0 0 0 2 1 0 0 0 3 2
10 6 2 0 0 1 4 3 3 4 0 2 0 3 1 3 2 7 5 5 6 1 3 3 4 0 0 1 1 3 0 0 1 1 4 2
11 5 7 3 2 1 2 4 4 1 3 0 2 0 2 1 0 5 3 2 4 1 3 2 3 1 1 2 1 0 0 1 2 4 0 1
12 7 3 0 1 3 1 1 3 0 0 2 1 1 1 3 2 6 4 4 5 2 2 5 5 1 0 2 1 0 1 0 2 1 1 2
13 6 5 1 3 0 2 4 3 2 1 1 1 2 2 2 1 7 4 4 6 1 4 2 4 0 1 1 2 0 1 1 1 3 1 3
14 6 4 0 0 1 4 0 1 1 0 3 2 1 2 2 0 6 4 4 5 2 2 4 4 0 2 0 2 1 0 2 0 2 2 0
15 5 6 2 1 2 2 1 3 0 0 1 0 2 1 3 1 7 5 2 6 1 1 4 4 0 0 0 3 2 0 0 2 4 2 0
16 7 5 1 1 2 2 1 3 3 2 2 3 1 2 2 0 6 3 3 4 2 2 5 5 1 2 0 1 1 0 2 1 2 1 0
17 6 5 1 0 0 5 4 3 0 3 2 2 1 2 2 0 6 3 4 4 1 3 3 4 1 1 2 0 1 0 3 2 0 1 1
18 4 3 0 0 3 2 2 4 2 3 1 4 1 1 3 2 6 1 3 2 1 1 3 4 4 0 0 0 1 0 0 1 2 2 0
19 7 2 0 1 1 3 3 2 1 1 3 2 2 3 1 0 6 4 5 5 3 4 3 5 2 0 0 1 1 1 1 0 1 1 2
20 5 2 0 0 3 2 0 3 1 1 1 1 4 1 3 0 6 6 3 6 1 1 4 4 1 0 0 0 2 2 0 2 0 3 2
21 6 5 2 2 0 3 5 3 4 1 1 2 2 2 2 0 6 4 3 5 1 4 2 4 1 1 1 0 1 1 3 1 1 1 2
22 6 3 1 1 3 1 2 2 3 2 3 3 1 1 3 2 6 2 4 4 2 3 3 4 2 1 1 0 1 0 1 1 1 2 0
23 5 7 2 2 0 3 4 2 1 4 2 3 0 2 2 0 5 2 4 4 1 3 2 4 1 2 1 1 0 0 3 1 3 0 0
24 6 6 1 2 2 1 3 3 1 1 2 0 1 1 3 0 6 4 4 5 2 3 3 5 0 0 3 2 0 0 0 3 3 0 1
25 5 4 0 0 2 3 1 4 2 1 1 2 1 1 2 1 6 3 3 4 0 2 3 4 2 0 1 1 0 1 0 2 2 2 1
26 6 4 0 2 1 2 3 3 2 1 2 0 3 3 1 0 7 5 5 6 2 3 3 5 0 0 1 2 1 1 0 1 3 2 2
27 5 6 1 1 0 4 3 3 1 2 2 2 0 1 3 1 5 3 4 4 1 3 2 3 1 1 1 2 0 0 1 1 4 0 1
28 6 6 1 1 0 4 1 2 3 1 3 3 0 1 3 0 6 3 4 4 1 3 3 4 0 3 0 2 0 0 3 1 2 0 0
29 6 7 2 2 0 3 4 3 0 2 1 1 1 1 2 0 7 4 2 5 2 4 2 4 0 1 2 1 0 1 3 2 2 0 1
30 6 4 1 0 2 3 0 3 1 0 2 3 1 2 1 0 6 3 3 4 1 2 4 3 1 2 0 1 1 0 2 1 1 1 0
31 8 3 0 1 1 3 2 3 1 2 2 3 1 3 1 0 6 3 4 4 3 5 3 5 2 1 0 1 0 1 1 1 1 1 1
32 5 1 0 0 3 2 1 3 3 0 2 2 2 2 2 0 6 4 4 5 2 2 3 4 2 0 0 1 2 0 0 0 1 4 0
33 6 4 0 4 1 0 4 2 2 2 2 0 4 1 3 1 8 6 4 7 2 4 2 5 0 0 0 2 1 2 0 1 3 1 3
I would want to have a count summary of all two entries of any two columns ONLY
Example if you take column 1 and 2 you can have 6 and 4 their total count is 5 (rows 1,14,26,30 and 33)
Another Example. If you take column 10 and 33 and you pick 2 and 2 their total count is 4 (rows 1,2,16 and 29) and on the same columns you can pick 2 and 1 and their total count is 2(rows 22 and 31)

I hope it is clear

 

[PAB]

Hi Kenya649,

I have the following table that has columns 1 to 35 and rows 1 to 33.

The rows are not as important as the columns are for this excercise.
Are you saying that you would like to pair EVERY column with EVERY other column and produce counts?
If so, and using 35 columns, there are a total of 595 different COMBINATIONS of columns.
Also, using numbers from 0 to 9 there are a total of 100 PERMUTATIONS, although I notice that you do not have any number 9's.
Is this what you are after or have I mis-interpreted your request?
I hope this helps!

Regards,
PAB


-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
Mathematics is the language of nature.
Everything around us can be represented and understood through numbers.
If you graph the numbers of any system, patterns emerge. Therefore, there are patterns everywhere in nature.

 

[PAB]

Hi Kenya649,

If you just wanted to compare ANY 2 columns I could upload a SpreadSheet that will give you the total count for ALL the PERMUTATIONS of those columns.
To change the columns to compare, all you would need to do is to change the criteria in cells AK1:AL1.

Out of interest, what does the data represent and what are you trying to achieve?

BTW, please reply to the first post first.

Regards,
PAB


-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
Mathematics is the language of nature.
Everything around us can be represented and understood through numbers.
If you graph the numbers of any system, patterns emerge. Therefore, there are patterns everywhere in nature.

 

[Kenya649]

Hi Kenya649,

Out of interest, what does the data represent and what are you trying to achieve?

Thank You PAB for your reply.

The data represent results of filters I have for a 5/56 (Mega Million) lottery game.

Kindly upload the spread sheet it might help me to achieve what I want.

Regards

Kenya649

 

[PAB]

Hi Kenya649,

If you just wanted to compare ANY 2 columns I could upload a SpreadSheet that will give you the total count for ALL the PERMUTATIONS of those columns.
To change the columns to compare, all you would need to do is to change the criteria in cells AL2:AM2.

I don't know what version of Excel you are using but this will work in Excel 2007 and HIGHER.
As I said above in the Quote, to change the columns to compare, all you would need to do is to change the criteria in cells AL2 AND/OR AM2.

MediaFire - Count Data - Kenya649

http://www.mediafire.com/view/?f069uybn6int5kx

BTW, MediaFire is a FREE Cloud storage service.

Please let me know what you think!

Regards,
PAB


-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
Mathematics is the language of nature.
Everything around us can be represented and understood through numbers.
If you graph the numbers of any system, patterns emerge. Therefore, there are patterns everywhere in nature.

 

[PAB]

Hi Kenya649,

I did check that the data output was correct for several comparisons.
If you wanted to check for yourself on say columns B & C, just copy and paste the values in cells AL3:AM102 to cells B3:C102 and the answers in cells AN3:AN102 will ALL equal 1 which is obviously correct.

Regards,
PAB


-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
Mathematics is the language of nature.
Everything around us can be represented and understood through numbers.
If you graph the numbers of any system, patterns emerge. Therefore, there are patterns everywhere in nature.

 

[Kenya649]

Hi Kenya649,

I did check that the data output was correct for several comparisons.
If you wanted to check for yourself on say columns B & C, just copy and paste the values in cells AL3:AM102 to cells B3:C102 and the answers in cells AN3:AN102 will ALL equal 1 which is obviously correct.

Thank you PAB,

I also got the following from another source


Example if you take column 1 and 2 you can have 6 and 4 their total count is 5
=SUMPRODUCT((A1:A33=6)*(B1:B33=4))

Another Example. If you take column 10 and 33 and you pick 2 and 2 their total count is 4
=SUMPRODUCT((J1:J33=2)*(AG1:AG33=2))

Regards

Kenya649

 

[PAB]

Hi Kenya649,

I also got the following from another source:

Example if you take column 1 and 2 you can have 6 and 4 their total count is 5:
=SUMPRODUCT((A1:A33=6)*(B1:B33=4))

Another Example. If you take column 10 and 33 and you pick 2 and 2 their total count is 4:
=SUMPRODUCT((J1:J33=2)*(AG1:AG33=2))

Yes, SUMPRODUCT as used in the above will give the correct results but only for the selected criteria you set.
BUT, if you want to calculate half a dozen or a dozen results this would mean changing the formula for each of the criteria you want. It just seems to be non productive to me when I setup a SpreadSheet that automatically produces ALL the results for ALL of the permutations of 2 numbers from 0-9 for ANY 2 columns you wish to set.

Regards,
PAB


-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
Mathematics is the language of nature.
Everything around us can be represented and understood through numbers.
If you graph the numbers of any system, patterns emerge. Therefore, there are patterns everywhere in nature.

 

[bloubul]

Hi PAB

How are you doing my friend I trust that I will find you in good health. I also need some help with a macro please. I will upload the file for you once you give me the go ahead. The excel file consists of 3 spreatsheets, MAIN (M), TARGET (T) which is not a problem because it must be manually updated there is no other way, but the spreadsheet called REPORT is where I needs your help.

Thanks in advance

BlouBul

 

[PAB]

Hi BlouBul,

Hi PAB

How are you doing my friend I trust that I will find you in good health. I also need some help with a macro please. I will upload the file for you once you give me the go ahead. The excel file consists of 3 spreatsheets, MAIN (M), TARGET (T) which is not a problem because it must be manually updated there is no other way, but the spreadsheet called REPORT is where I needs your help.

Thanks in advance,

BlouBul

I am very well thanks BlouBul as I hope you and your wife are.
With regard to your request, I will be happy to have a look at the file for you.
BUT, you will need to start a new thread for this as this thread is obviously dealing with another topic.

Regards,
PAB


-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
Mathematics is the language of nature.
Everything around us can be represented and understood through numbers.
If you graph the numbers of any system, patterns emerge. Therefore, there are patterns everywhere in nature.