2

I can't embed images yet, but I have included them as inline links.

In Google Sheets, I have built a set list builder for my band. Currently, I have dropdowns that allow the user to select a song from our song list. I want these dropdowns to be dynamic, and only allow the user to select songs that have not already been selected.

have not already been selected.

Because we usually have 3 sets in a night, I need to query the three "set" columns to see if a specific value has been selected in any of the 3 columns, and only include a song in the list if it hasn't been selected in any columns, to prevent anyone from selecting the same song twice.

I was able to make this work using a IF(ISNA(VLOOKUP)) function

IF(ISNA(VLOOKUP)) function

for a single column, but I can't figure it out for multiple rows in multiple columns.

The current query I am using is:

=ARRAYFORMULA(UNIQUE(QUERY(TO_TEXT({A:C}),"select Col1 where Col1 is not null")))

This formula works for the first column, A (set 1), but not columns B or C (set 2 or 3).

Link to the spreadsheet: https://docs.google.com/spreadsheets/d/18i3AgDOU4zIqsmZQaaA5HiGOfwLA0BoFB4qBa-Tl1SI/edit?usp=sharing

player0
  • 124,011
  • 12
  • 67
  • 124
Skyler
  • 21
  • 2

1 Answers1

1

You are creating Set lists based on a list of songs. Up to three Sets are created. The overarching rule is that no song should be played more than once. Therefore the available songs for Sets 2&3 are dependant on the preceding each Set. Your formulae for keeping track of songs in each set and the unplayed songs is not working for Sets 2 & 3.

This answer is not a minor adjustment to a specific formula. There is a flaw in identifying the available songs and the songs chosen for each set.

Sheet"DO NOT TOUCH" (Available songs)

The structure of Sheet"DO NOT TOUCH" needs to reflect the songs available and the songs chosen for each Set.

Do Not Touch

The formula that apply in this layout are:

  • Column A: =query(SONGS!B3:B,"select *") (Cell A2). List all the available songs.
  • Column B: =query(SET_1,"select *") (Cell B2). List the songs selected for Set1.
  • Column C: =filter(Set1Available,len(Set1Available),iserror(match(Set1Available,B2:B,0))) (Cell C2). Compare i)available songs and ii)songs from Set1; list songs not included in Set1.
  • Column D: =query(SET_2,"select *") (Cell D2). List the songs selected for Set2.
  • Column E: =filter(Set2Available,len(Set2Available),iserror(match(Set2Available,D2:D,0))) (Cell E2). Compare i)available songs for Set2 and ii)songs from Set2, list songs not included in Set2.
  • Column F: =query(SET_3,"select *") (Cell F2). List the songs selected for Set3.
  • Column G: =filter(Set3Available,len(Set3Available),iserror(match(Set3Available,F2:F,0))) (Cell G2. Compare i)available songs for Set3 and ii)songs from Set3 list songs not included in Set3.

Note: I created named ranges for Set1Available ('DO NOT TOUCH'!A2:A62), Set2Available ('DO NOT TOUCH'!C2:C62), and Set3Available ('DO NOT TOUCH'!E2:E62).

WORKING-SET1/SET2/SET3

The Data validation for each Set must change; this is the reason that your formula didn't work. - SET1: Set1Available - SET2: Set2Available - SET3: Set3Available

SETS

SETS

You'll notice in this screencap that "ALIVE" is included in Sets 1 & 3. It was originally added to Set 3 (when it was a valid selection), and later it was added to Set1. The entry in Set3 is now invalid it displays the red invalid data indicator.

FWIW, I also noticed that the "Time Est" formula was not consistent in every row; you may wish to revisit this.

Props: Compare two lists and return only those values that are missing from one list

Tedinoz
  • 5,911
  • 3
  • 25
  • 35