# Formula/Code to tell me who has correct numbers?

#### drc2265

##### Board Regular
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

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

##### MrExcel MVP
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.

#### drc2265

##### Board Regular
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!!

##### MrExcel MVP
The only thing that did not work was the H2 formula,

That formula needs control+shift+enter. How did it not work?

the old one worked fine so I used that... other than that, awesome!! thanks a lot!!

You are welcome.

#### drc2265

##### Board Regular
I tried Crtl Shtf Ent and every number was a 1 In the total column, maybe i did it wrong.

#### drc2265

##### Board Regular
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!

##### MrExcel MVP
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.

#### drc2265

##### Board Regular
great thanks alot!!

Replies
0
Views
131
Replies
0
Views
179
Replies
6
Views
177
Replies
1
Views
261
Replies
3
Views
121

1,190,916
Messages
5,983,572
Members
439,850
Latest member
suhailrocks786

### 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.

### Which adblocker are you using?

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

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