Creating Multiple Drop Downs with No Repetition of Values

Hi Everyone,

I think the picture explains everything whats gonna be in this articles. Yes, Multiple dropdowns with no Repetited Values with only one helper column And lesser and quite easy steps. So here we go.

First we will make some Named Ranges to Capture the Players Pool and Selected Pool. Use Following Non Volatile Dynamic Named Ranges which will Expand and Contract those Pools when Data is added or cut off.

Note:- While Making these Named ranges, you have to select any cell in the first row. This is Important as behaviour of the Named Ranges Depends up on that.

Players (in Column A)

=Sheet1!$A$2:INDEX(Sheet1!$A:$A,MATCH(REPT("Z",100),Sheet1!$A:$A,1))

Selected(in Column D)

=Sheet1!$D$2:INDEX(Sheet1!$D:$D,MATCH(REPT("Z",100),Sheet1!$D:$D,1))

Now in the Next part, Make a Named Range to get an array of Corressponding Row Numbers of Unselected Players in the Players Pool as follows:-

RowArr

=IF(COUNTIF(Selected,Players),FALSE,ROW(Players))

Explanation:-

The COUNTIF(Selected,Players) Part Counts each Player of the Players Pool in Selected Pool and returns an array, having count of Each Player (in Players Pool), of same Size or Dimensions as of Player Pool. If I calculate this Part on Excel Sheet itself then the Returned Array would be (having regard to the Selected Pool that Includes one Player Ie. C Ronaldo) :- 

0
1		<----as C Ronaldo has already been selected, so count comes out to be one.
0
0
0
0
0
0
0

Now If Function(If(Test,Value if True, Value if False)) will parse this Array to one below and in the following way:-

Countif Array		Row Array (ROW(Players))			Resultant Array
0		2			2	Reason1
1		3			FALSE	Reason2
0		4			4	Reason1
0		5			5	Reason1
0		6			6	Reason1
0		7			7	Reason1
0		8			8	Reason1
0		9			9	Reason1
0		10			10	Reason1

Reasons:-

1. 0 is an equivalent to FALSE. So if function returns the Corressponding Row Number.

2. 1 is an equivalent to TRUE. So if Function returns the Value if TRUE that is False.

This Resultant is stored in the Named Range - RowArr and gets changed whenever a new Player is selected in the Column D or we can say, New Items gets added to the Selected Pool.

Now, We will use a helper column to list unselected Players using Row numbers in the RowArr.

Put this formula in the cell F2 and Drag down untill the last filled cell of Column A to cover all possibilities.

=IFERROR(INDEX($A:$A,SMALL(RowArr,ROW($A1))),"")

The ROW($A1) function(equivalent to 1) has been used as nth Small parameter of Small Function which increments by one as Formula is Dragged down. The Small function returns the 1st, 2nd, 3rd Small value in the RowArr Array and so on, as Formula is Dragged down. This Small value become the Row_Num parameter of the INDEX function and the Unselected Player is returned.

Now making one last Named Range to Cover the Unselected items listed in Helper column.

ItemsLeft:-

=Sheet1!$F$2:INDEX(Sheet1!$F:$F,MATCH("",Sheet1!$F:$F,0)-1)

Now Select D2:D6 Range and click Data Tab ---> Data Validation and write by selecting List option from the Dropdown.

=ItemsLeft

That's how the dropdowns will get activated. Now select the Players from the Dropdowns and you will notice that the Already Selected Players are being deducted from the next cell Dropdown List.

That was all for the Explanation of this technique. However, if you all have any doubt or query then do comment.

Check the attached example file:-

DropDowns with No Repetition.xlsx

you can see some advanced application in the Post #10 of the link below:-
http://www.excelforum.com/excel-programming-vba-macros/1065292-vba-to-remove-asset-from-pool-column-after-choosing-it-in-dropdown-menu.html

Regards,

Vikas Gautam