# Selecting first available pick from a list of data in a Nascar Pool

#### AM-I-AM

##### New Member
Hello! I am using Excel 2007 to work with a group of 18 players in a Nascar pool. Each player selects between 1 to 18 car numbers (out of a list of 43) based upon their rank for the week. I am trying to create a spreadsheet that will select the first available car from their personal list of picks that looks at the previous rank's numbers so there is no duplication. I have tried using If then nesting functions, but it became cumbersome. Please give some ideas as to the most effcient manner in which we can complete this project.
Below is an example of the table for the picks- I would like to be able to have a formula that will select the next non duplicated number in a person's pick. Example- PERSON3 would have 24 as their selection- since 33 was already selected by PERSON1. Any help would really really be appreciated. THANK YOU!

 Place Name Selection Pick1 Pick2 Pick 3 Pick 4 Pick5 Pick6 Pick7 Pick8 Pick9 Pick10 Pick11 Pick12 Pick13 Pick14 Pick15 Pick16 Pick17 Pick18 1 PERSON1 33 33 2 PERSON2 9 33 9 3 PERSON3 33 24 18 4 PERSON4 33 7 9 27 5 PERSON5 2 35 29 28 5 6 PERSON6 7 PERSON7 8 PERSON8 9 PERSON9 10 PERSON10 11 PERSON11 12 PERSON12 13 PERSON13 14 PERSON14 15 PERSON15 16 PERSON16 17 PERSON17 18 PERSON18

<TBODY>
</TBODY>

### Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Welcome to the Board!

So your expecting the following results?

33
9
24
7
2

This may have to be done with VBA code as I am not positive it can be done with native formulas or at least I cannot think of a way except with a lot of IF statements. Is VBA an acceptable solution?

Yes- VBA would be acceptable. I've not worked with it alot, but I'm willing to give it a go! THANK YOU! for your response! My boss has put me up to this, and I'm just not sure where to go... And yes the results from above are what I am looking for-

Here is a UDF that should work:

Code:
``````Function getpicks(pickRange As Range, selectionRange As Range)
Dim r As Range, foundRange As Range
For Each r In pickRange
Set foundRange = selectionRange.Find(r.Value, lookat:=xlWhole)
If foundRange Is Nothing Then
getpicks = r.Value
Exit For
End If
Next r
End Function``````
Excel Workbook
ABCDEFGHIJK
1PlaceNameSelectionPick1Pick2Pick 3Pick 4Pick5Pick6Pick7Pick8
21PERSON13333
32PERSON29339
43PERSON324332418
54PERSON47337927
65PERSON5223529285
76PERSON6
87PERSON7
98PERSON8
109PERSON9
1110PERSON10
1211PERSON11
1312PERSON12
1413PERSON13
Sheet1
Cell Formulas
RangeFormula
C3=getpicks(D3:U3,\$C\$2:C2)
C4=getpicks(D4:U4,\$C\$2:C3)
C5=getpicks(D5:U5,\$C\$2:C4)
C6=getpicks(D6:U6,\$C\$2:C5)

Hope that helps.

Thank you so much! I will try to work with it at lunch- I LOVE THIS BOARD- YOU ROCK!

Ok... Not really understanding what I am doing here. So sorry to be a newbie! Am I supposed to define the Range and name it... I copied the formulas in the worksheet and copied it down- but not really sure what it is doing. Also am I creating a macro or VB? Any help would be appreciated. Thank yoU!

This is a user-defined function. Basically from within the workbook you will press Alt+F11 and that will bring you to the visual basic screen. You will want to add a standard module and then place this code within the standard module.

Then this formula will be able to be used in this workbook and this workbook only. It will need to be saved as an .xls or .xlsm (cannot be .xlsx).

Hopefully that helps you as you do not need to run any code with this, its just creating a custom formula.

Thank you for explaining it a little further to me- The only other issue is that there is duplication in car numbers...

 Place Name Selection Pick1 Pick2 Pick 3 Pick 4 Pick5 1 PERSON1 33 33 2 PERSON2 9 9 33 3 PERSON3 9 33 9 18 4 PERSON4 9 33 9 7 27 5 PERSON5 2 2 35 29 28 5

<TBODY>
</TBODY>

<TBODY>
</TBODY>

Here's a copy of the cell formulas-
 Place Name Selection Pick1 Pick2 Pick 3 Pick 4 Pick5 1 PERSON1 33 33 2 PERSON2 =getpicks(D3:U3,\$C\$2:C2) 9 33 3 PERSON3 =getpicks(D4:U4,\$C\$2:C3) 33 9 18 4 PERSON4 =getpicks(D5:U5,\$C\$2:C4) 33 9 7 27 5 PERSON5 =getpicks(D6:U6,\$C\$2:C5) 2 35 29 28 5

<TBODY>
</TBODY>

and the module-

Function getpicks(pickRange As Range, selectionRange As Range)
Dim r As Range, foundRange As Range
For Each r In pickRange
Set foundRange = selectionRange.Find(r.Value, lookat:=xlWhole)
If foundRange Is Nothing Then
getpicks = r.Value
Exit For
End If
Next r
End Function

What am I not doing right?

You are doing everything right, I just missed a small part

Rich (BB code):
``````Function getpicks(pickRange As Range, selectionRange As Range)
Dim r As Range, foundRange As Range
For Each r In pickRange
Set foundRange = selectionRange.Find(r.Value, LookIn:=xlValues, lookat:=xlWhole)
If foundRange Is Nothing Then
getpicks = r.Value
Exit For
End If
Next r
End Function``````

Worked!!!!!! Thank you Thank you!! I am going to do some research on User-defined functions. All of your help appreciated- Now I can sleep!

Replies
8
Views
871

1,196,483
Messages
6,015,461
Members
441,898
Latest member
kofafa

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