I have decided to share my system since there are so many people requesting their state be done. Now you can do your own. If you can tweak this, please do so & let us all know.
Also, if you can share numbers within the forum that you obtain from this-Please do so.
To set up in excel
B1(DATE)
C1 (DRAW)
D1 (a) E1(b) F1(c)
Column G has been made to become width .17
Add to Column H1 thru M1 ( ab, ac, ba, bc, ca, cb )
O1(root)
P1(sum)
Q1(Doubles)
R1(pairs)
S1(Singles or “whatever you wish to call other pairs”)
T1( pairs)
Now for formulas
D2 =SUM(1*(LEFT(C2,1)))
E2=SUM(1*(MID($C2,2,1)))
F2=SUM(1*(MID($C2,3,1)))
H2=CONCATENATE(D2,E2)
I2=IF((CONCATENATE(D2,F2)=H2),"",CONCATENATE(D2,F2))
J2=IF((OR((CONCATENATE(E2,D2)=H2),(CONCATENATE(E2,D2)=I2))),"",CONCATENATE(E2,D2))
K2=IF((OR((CONCATENATE(E2,F2)=H2),(CONCATENATE(E2,F2)=I2),(CONCATENATE(E2,F2)=J2))),"",CONCATENATE(E2,F2))
L2=IF((OR((CONCATENATE(F2,D2)=H2),(CONCATENATE(F2,D2)=I2),(CONCATENATE(F2,D2)=J2),(CONCATENATE(F2,D2)=K2))),"",CONCATENATE(F2,D2))
M2=IF((OR((CONCATENATE(F2,E2)=H2),(CONCATENATE(F2,E2)=I2),(CONCATENATE(F2,E2)=J2),(CONCATENATE(F2,E2)=K2),(CONCATENATE(F2,E2)=L2))),"",CONCATENATE(F2,E2))
O2=IF((SUM((LEFT($C2,1)*1)+(MID($C2,2,1)*1)+(RIGHT($C2,1)*1)))>9,(SUM((LEFT((SUM((LEFT($C2,1)*1)+(MID($C2,2,1)*1)+(RIGHT($C2,1)*1))),1)*1)+(RIGHT((SUM((LEFT($C2,1)*1)+(MID($C2,2,1)*1)+(RIGHT($C2,1)*1))),1)*1))),(SUM((LEFT($C2,1)*1)+(MID($C2,2,1)*1)+(RIGHT($C2,1)*1))))
P2=SUM(D2:F2)
Q2 (**)
R2(Cnt)
Q3 thru Q13
00
11
22
33
44
55
66
77
88
99
R3 =COUNTIF($H$2:$M$500,Q3) then copy down to R13
*note that you can change the range from 500 to whatever range you choose*
S2 (C)
T2 (Cnt)
S3 thru S47 (add your pairs)
T3 =COUNTIF($G$2:$M$560,S3) then copy down to S47
Change Column U to a width of .17
V1 Label as Key numbers
To V2 thru V11 (add 0 thru 9)
W2 =COUNTIF($D$2:$F$14,W2)
Copy down to W11
*note that I have chosen a range of 2wks. The range within formula on W2 can be changed to whatever day selection that you choose*
Enjoy!
Questions need to be asked within this thread so this everyone can benefit from the answers
Credit for some formulas should be given to SirMetro