Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula
Hi,
I have a golf league and I am trying to create a worksheet and write a formula that will tell me how many times golfer Jim plays with every other golfer through his 5 rounds. Also how many times golfer Sam plays with every other golfer and so on. We have 12 players so each row is a foursome, and there are 3 foursomes per day. We are playing 5 rounds. I am trying to do the calc in columns G thru R and rows 2 thru 14 My sheet is as follows: A B C D E F G H I J K L 1 Jim Sam Bob Tom Jim Sam Bob Tom Dave etc..... 2 Dave Mike Tony Alan Jim x 1 1 1 0 3 Ron Darin Joel Scott Sam 1 x 2 1 0 4 Bob 1 1 x 1 1 5 Sam Tony Scott Mike etc... 6 Alan Jim Ron Darin 7 Bob Tom Dave Joel 8 9 .. .. .. etc Thanks for your help, Mike |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula
Not sure what you are dealing with there, but try this:
Jim in Column A; other names below. Ron in Column B; other names below. D1 = Jim E1 = Ron F1 =SUMPRODUCT((A1:A18=D1)*(B1:B18=E1)) Another possible solution: Names in Column A and Column B; some matches and some non-matches. F1 =IF(NOT(ISERROR(MATCH(A1:A6,B1:B6,0))),A1:A6,"") Commit this with Ctrl+Shift+Enter (not just Enter). Fill down... Or... F1 =IF(ISERROR(MATCH(A1:A6,B1:B6,0)),A1:A6,"") Ctrl+Shift+Enter...gives the opposite result. HTH, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Michaelt721" wrote: Hi, I have a golf league and I am trying to create a worksheet and write a formula that will tell me how many times golfer Jim plays with every other golfer through his 5 rounds. Also how many times golfer Sam plays with every other golfer and so on. We have 12 players so each row is a foursome, and there are 3 foursomes per day. We are playing 5 rounds. I am trying to do the calc in columns G thru R and rows 2 thru 14 My sheet is as follows: A B C D E F G H I J K L 1 Jim Sam Bob Tom Jim Sam Bob Tom Dave etc..... 2 Dave Mike Tony Alan Jim x 1 1 1 0 3 Ron Darin Joel Scott Sam 1 x 2 1 0 4 Bob 1 1 x 1 1 5 Sam Tony Scott Mike etc... 6 Alan Jim Ron Darin 7 Bob Tom Dave Joel 8 9 . . . etc Thanks for your help, Mike |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Regression Leverage Formula (Jerry W. Lewis or Mike Middleton)already have DFITS formula | Excel Worksheet Functions | |||
Excel 2002 Formula: Urgent Conditional Formula Required Right Away - if possible | Excel Discussion (Misc queries) | |||
Formula expected end of statement error, typing formula into cell as part of VBA macro | Excel Programming | |||
Cell doesn't show formula result - it shows formula (CTRL + ' doe. | Excel Worksheet Functions | |||
Commenting custom formula fields/formula on formula editor | Excel Programming |