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

[AM-I-AM]

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>

 

[schielrn]

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?

 

[AM-I-AM]

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-

 

[schielrn]

Here is a UDF that should work:

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
	A	B	C	D	E	F	G	H	I	J	K
1	Place	Name	Selection	Pick1	Pick2	Pick 3	Pick 4	Pick5	Pick6	Pick7	Pick8
2	1	PERSON1	33	33
3	2	PERSON2	9	33	9
4	3	PERSON3	24	33	24	18
5	4	PERSON4	7	33	7	9	27
6	5	PERSON5	2	2	35	29	28	5
7	6	PERSON6
8	7	PERSON7
9	8	PERSON8
10	9	PERSON9
11	10	PERSON10
12	11	PERSON11
13	12	PERSON12
14	13	PERSON13
Sheet1

Cell Formulas
Range	Formula
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.

 

[AM-I-AM]

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

 

[AM-I-AM]

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!

 

[schielrn]

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.

 

[AM-I-AM]

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?

 

[schielrn]

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

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

 

[AM-I-AM]

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!