I have a list of songs. Each song has a list of names of all the people needed to perform that song.

E.g.

Song1 | Jess | Julia | Mandy | Fred | Simon | 
Song2 | Jess | Julia |       | Fred | Simon |
Song3 |      | Julia |       | Fred | Simon |

I then have a list of people that are present.

E.g

Jess | Julia | Fred | Simon 

I want to compare the list of people that are present against the list of songs so we know which songs can be performed.

If we are comparing the two examples we know that Song2 and Song3 can be performed but not Song1. Song2 because Jess, Julia, Fred, and Simon are present. Song3 because Julia, Fred, Simon are present (Jess is there but not needed). Song1 shouldn't be returned since Mandy is not present.

I would like the song titles that will be in the first column returned.

So far I have something like this but I know its not right:

=IF(REGEXMATCH(A1:1, B2:E2), A2, "Nope")

Where A1:1 is the row with the people present and B2:E2 is the people needed to perform the song. A2 is the title of the song.

I originally tried:

=IF(A1:1=B2:E2, A2, "Nope")

But knew that wouldn't work since it is comparing for exact matchings.

Thank you.

Edit:

If I were to write this as code it would probably be something like this.

const presentSingers = ['Julia', 'Mandy', 'Jess', 'Fred']
const song1 = ['Julia', 'Mandy']
const song2 = ['Julia', 'Mandy', 'Jess', 'Fred']
const song3 = ['Julia', 'Rachel', 'Mandy']
const allSongs = [song1, song2, song3]

const songsThatCanBePerformed = []

allSongs.forEach((song) => {
   let present = true
   song.forEach((singer) => {
      if (!singers.includes(singer)) {
         present = false
      }
   })
   if (present) {
      songsThatCanBePerformed.push(song)
   }
})

console.log(songsThatCanBePerformed)

CodePudding user response：

Try this formula:

=AND(NOT(ISNA(HLOOKUP(B2:INDEX(B2:F2,COUNTA(B2:F2)),$A$7:$D$7,1,FALSE))))

• Formula will return an array of either the singer name or #N/A depending on finding the name in the song requirement
• B2:INDEX(B2:F2,COUNTA(B2:F2)) ensures that we do not include the blanks in our data.
  • Assumes names are listed sequentially from left-to-right with not intervening empty cells.
  • If that is not the case, a more complex formula can be devised.
• NOT(ISNA(... changes that to an array of booleans
• AND ensures that every element in the array is TRUE for Can Perform to test TRUE

Note: In early versions of Excel, and in Google Sheets, this must be entered as an array formula; either with ctrl shift enter in Excel or, in Sheets as =ArrayFormula(...

CodePudding user response：

You could use this formula

=if(average(countif(F4:J4,vlookup(F4,N4:N7,1,false)),countif(F4:J4,vlookup(G4,N4:N7,1,false)),countif(F4:J4,vlookup(H4,N4:N7,1,false)),countif(F4:J4,vlookup(I4,N4:N7,1,false)),countif(F4:J4,vlookup(J4,N4:N7,1,false)))<1,"Cannot Perform","Can Perform")

where

F4:J4 = represents the performers required

H4:N7 = represents the attendance of the performers

in short , this formula works by returning 1 if the performers are present and getting the average of the attendance, everyone is present the average would be 1 and if not it would be less than 1.

if you do not want a complete match then you can use wildcharacters in your vlookup search.

CodePudding user response：

Google Sheets supports a scripting language that allows you to write "JavaScript-like" code.

Formula in H1:

=FILTER(A1:A3,MMULT(ISNA(MATCH(B1:F3,J1:M1,0))*(B1:F3<>""),SEQUENCE(5,1,1,0))=0)

CodePudding user response：

Here something that's quite simple, yet adaptable, (and the simplest I could come up with so far):

Using B2 as helper cell to concatenate the "artists present" list 
{=IF(SUM(IF(B5:H5<>"",IF(ISERROR(FIND(B5:H5,$B$2)),0,1)))=COUNTA(B5:H5),"Yes","Nope")}

Note the {}: This is an array formula.

Pictogram: