CODEWITHSUNDEEP
if-statementgoogle-sheetsfilterconditional-formattingmultiple-conditions
Lourenço Dias Amador
Lourenço Dias Amador

Reputation: 1

How to return names as a list within a cell, from formula with multiple conditions

So, trying to be as clear as I can, sorry if it isn't...

I have a spreadsheet to organise a lacrosse team roster screenshot of spreadsheet

In the box for "players that can play both shorty & pole" in N5:O16, I have a list of player names. This is taken from column K, which has an IF formula to return names when the player matches the condition in the box-title, through playing both a position from columns D-F and a position from column G or H. I want to get this equation, used in each cell in column K, and put it within the formula in the box, so that I can get rid of column K....

As you can see in the image, columns D to I are tick boxes for different positions that each player may play. Column K then shows which players play both positions with short sticks (columns D-F) and with long poles (G and H). It shows this by returning their names in column K, as long as they play both one position in column D-F, and one position in column G or H. The formula I used for this is:

=IF(AND(OR(D4:D28=TRUE,E4:E28=TRUE,F4:F28=TRUE),OR(G4:G28=TRUE,H4:H28=TRUE)),B4,)

Then in a box in N5:O16, I show those names in a list with the following formula: =JOIN(", ",FILTER(K4:K28,K4:K28<>M5))

^in this formula, M5 is just a blank cell.

What I'm trying to do is, if possible, have a formula in that box in N5:O16 that shows that list with those names, without having to rely on column K, i.e. I wanna get rid of column K, without losing that list.

I tried to use a similar formula to the one in the box in L5:L16, where it returns the numbers of jerseys that are still available, i.e. no names associated. This is the formula I used for it: =JOIN(", ",TRANSPOSE(FILTER(A4:A28,$B$4:$B28=M5)))

But I can't seem to figure out how to do this for the list of players... any suggestions?

Thanks!!

Upvotes: 0

Views: 304

Answers (1)

FBrTeach
FBrTeach

Reputation: 38

The following formula should work.

=ArrayFormula(
        JOIN(", ",
             FILTER(B3:B15,
                 {
                      BYROW(D3:F15,
                            LAMBDA(shorty,countif(shorty,"="&TRUE)>0))*
                      BYROW(G3:H15,
                            LAMBDA(long,countif(long,"="&TRUE)>0)
                      )
                 }>0
              )
         )
    )

The ByRow allows you to perform a calculation to a whole row. Each ByRow counts for the amount of TRUE values in their range. The * performs an AND operation, so both shorty and long in your case.

We then filter your name column based on this combined TRUE/FALSE value, then join that returned value into one cell.

Upvotes: 0

Related Questions