Formula/Code to tell me who has correct numbers?

drc2265

Board Regular
Joined
Jul 30, 2007
Messages
96
I have a sheet where the players select 6 numbers, and if they get the correct numbers, they get a "1" in the total column, if they get 1-5 correct, they get a “0” in the total column. Every player selects 6 numbers out of 28 total, 14 of them are correct. I have the correct numbers at the top of the sheet and have a formula automatically telling me if they got all 6 right or not. What Im asking is: Is there a code that I can put in a cell at the top that will tell me which player #s have won so I can just simply look at the cell and tell who won? There are 221 players, column A is ranged A8-A228 and the total column is in Column K.

1..3..6..8..9..11..13..16..17..19..21..24..25 - Correct numbers

PLAYER--Pick 1--Pick 2--Pick 3--Pick 4--Pick 5--Pick 6----Total
----1-------1--------3--------8-------17-----21-------25--------1
----2-------2--------4--------7-------19-----22-------25--------0

So in the cell telling me who won, It would have to tell me the Player number only. I dont know if this is possible, but if you could help me that would be great, thanks a lot!
Would be good to have a column of winners:
Winners:
1
132
201

Thank you for all your help.
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
I have a sheet where the players select 6 numbers, and if they get the correct numbers, they get a "1" in the total column, if they get 1-5 correct, they get a “0” in the total column. Every player selects 6 numbers out of 28 total, 14 of them are correct. I have the correct numbers at the top of the sheet and have a formula automatically telling me if they got all 6 right or not. What Im asking is: Is there a code that I can put in a cell at the top that will tell me which player #s have won so I can just simply look at the cell and tell who won? There are 221 players, column A is ranged A8-A228 and the total column is in Column K.

1..3..6..8..9..11..13..16..17..19..21..24..25 - Correct numbers

PLAYER--Pick 1--Pick 2--Pick 3--Pick 4--Pick 5--Pick 6----Total
----1-------1--------3--------8-------17-----21-------25--------1
----2-------2--------4--------7-------19-----22-------25--------0

So in the cell telling me who won, It would have to tell me the Player number only. I dont know if this is possible, but if you could help me that would be great, thanks a lot!
Would be good to have a column of winners:
Winners:
1
132
201

Thank you for all your help.
aaSeriesMatch.xls
ABCDEFGHIJK
1Correct NumbersPlayerPick-1Pick-2Pick-3Pick-4Pick-5Pick-6Won?Count
21Player-113162124252802
33Player-22791113150Winners
45Player-3511172123271Player-3
57Player-43715172391Player-4
69 
711
813
915
1017
1119
1221
1323
1425
1527
Sheet1


H2:

Control+shift+enter...

=1-OR(ISNA(MATCH(B2:G2,CorrectNumbers,0)))

CorrectNumbers refers to: A2:A15.

K2:

=SUM(I2:I5)

K4:

Control+shift+enter...

=IF(ROWS($K$4:K4)<=$K$2,INDEX($B$2:$B$5,SMALL(IF($I$2:$I$5=1,ROW($I$2:$I$5)-ROW($I$2)+1),ROWS($K$4:K4))),"")

and copy down.
 
Upvote 0
The only thing that did not work was the H2 formula, the old one worked fine so I used that... other than that, awesome!! thanks a lot!!
 
Upvote 0
I tried Crtl Shtf Ent and every number was a 1 In the total column, maybe i did it wrong.
 
Upvote 0
On another sheet like this, I have everybodies numbers (this sheet, each player chooses 13 numbers and i have a code that adds how many each player got right.) Is there anyway to tell me who has the least amount correct? The other code wouldnt work because on this sheet the total columns consist of numbers between 0-13, unlike just 1 from the other sheet. I would like the same setup as you did the other one if possible. Thanks!
 
Upvote 0
On another sheet like this, I have everybodies numbers (this sheet, each player chooses 13 numbers and i have a code that adds how many each player got right.) Is there anyway to tell me who has the least amount correct? The other code wouldnt work because on this sheet the total columns consist of numbers between 0-13, unlike just 1 from the other sheet. I would like the same setup as you did the other one if possible. Thanks!
aaSeriesMatch+LeastPerformers drc2265.xls
ABCDEFGHIJK
1Correct NumbersPlayerPick-1Pick-2Pick-3Pick-4Pick-5Pick-6CorrectCountSmallest Score
21Player-113162124252833
33Player-22791113155Count
45Player-35111721232762
57Player-48715221093Least Performers
69Player-1
711Player-4
813 
915 
1017
1119
1221
1323
1425
1527
16
Sheet1


I2, copied down:

=SUMPRODUCT(ISNUMBER(MATCH(C2:H2,CorrectNumbers,0))+0)

K2:

=MIN(I2:I5)

K4:

=COUNTIF(I2:I5,K2)

K6:

Control+shift+enter...

Code:
=IF(ROWS($K$6:K6)<=$K$4,
      INDEX($B$2:$B$5,
        SMALL(IF($I$2:$I$5=$K$2,
                     ROW($I$2:$I$5)-ROW($I$2)+1),
                   ROWS($K$6:K6))),
      "")
and copy down.
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,981
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top