Removing a selection from a dropdown list

I am building an APP for an annual junior (participants under 21) competition. There is a list of eligible entries tagged with unique number identifiers. Using this APP, the public will be able to vote and each voting person will submit once after they pick their ‘Top 5’ from 1st to 5th from a dropdown list of all eligible entries. Then the APP/Google Sheets will calculate the results and determine how the entries are placed from 1st to 5th based on the highest rankings for each placing from the aggregated placings submitted from each voter.

The issue I am having is that I have an APP tab for voting in which the layout is there is a dropdown of all of the unique number identifiers available for each placing, for which, a voter will make one selection from this same list for each placing from 1st through to 5th.

Voter APP layout Example:
1st Place > Dropdown List of eligible entry numbers
2nd Place > Dropdown List of eligible entry numbers less the number selected for 1st Place
3rd Place > Dropdown List of eligible entry numbers less the number selected for 1st & 2nd Place
4th Place > Dropdown List of eligible entry numbers less the number selected for 1st, 2nd & 3rd Place
5th Place > Dropdown List of eligible entry numbers less the number selected for 1st, 2nd, 3rd, & 4th Place

What I need to do is when a voter makes a selection for their 1st Place choice, then moves to selecting their 2nd Place choice, I want to remove the number that was selected for 1st Place, from the list of available choices for 2nd Place, and so on down to 5th Place, so that the same entry cannot be selected more than once from the dropdown list for each placing.

The APP will only submit each voter’s selections once after they have made all of their selections from 1st through to 5th. This Expression will have to run in the APP, but, I have no idea how to create this Expression for this purpose.

The APP has an ‘Admin’ Workbook with a Worksheet for recording all of the entry data (Entries) and a Worksheet for selecting status of the competition (Status) to control the time period for accepting entries and votes. A second Workbook with a Worksheet for collecting the votes (Votes) and a Worksheet for running placing calculations (Results).

I would sincerely appreciate your assistance. Thank you.

0 7 1,142
7 REPLIES 7

Austin
Participant V

So 2 questions do you have a column for 1st, 2nd, etc or is it 5 rows with a placement column?
If you have 5 columns for their 1st,2nd,etc then you can use the valid if to eliminate the previous answers. So your formula would look like:
SELECT(Choices[Name]) for 1st place column
SELECT(Choices[Name])-LIST([1st Place]) for 2nd place
SELECT(Choices[Name])-LIST([1st Place],[2nd Place]) for 3rd and so on.

Choices[Name] would be whatever list you’re using for eligible entries.

If you have a row per placement then its a bit more involved but you would do the same valid if:
SELECT(Choices[Name])-SELECT(Picked[Name])

My formula sucks for this one but basically SELECT eligible entries - SELECT entries already chosen.

Thank you very much, Austin. Your suggestion worked perfectly! Awesome!

FYI: I had used the same Expression but excluded “LIST” and the Expression kind of worked, except, it subtracted all of the previously entered values already submitted in the affected Worksheet column.

Lynn
Participant V

Hi
I have been trying to do something similar and I can get
First place with valid if Show Results[Horse_Rider]
for SECOND place I have valid if
SELECT(Show Results[Horse_Rider],[CLASS#]=[_THISROW].[CLASS#])-SELECT(Results[FIRST],[FIRST]=[_THISROW].[FIRST]) and this works.
For THIRD place I want the list minus FIRST and SECOND
I have tried variations of this but I am having trouble getting it to work
SELECT(Show Results[Horse_Rider],[CLASS#]=[_THISROW].[CLASS#])-(SELECT(Results[FIRST],[FIRST]=[_THISROW].[FIRST]),-SELECT(Results[SECOND],[SECOND]=[_THISROW].[SECOND]))
Thanks for any ideas on what might work.

Have you tried:

  • SELECT(...) - LIST([FIRST])
  • SELECT(...) - LIST([FIRST], [SECOND])
  • SELECT(...) - LIST([FIRST], [SECOND], [THIRD])

Hi @Steve
No I havent tried that but I will. Thank you .

Lynn
Participant V

Hi @Steve
That works brilliantly. I havent done much with LIST before. You are Champion, thanks.

Happy to help, @Lynn!

Top Labels in this Space