Forum Discussion

RBoyerr's avatar
RBoyerr
Copper Contributor
Aug 19, 2022
Solved

Having trouble printing names from one column into another table's rows If a condition is met

PC/Windows 10 Pro

Excel Version 2207 (Build 15427.20210)

Sample File 

 

Note: this workbook was copied from the original which is linked to a Form via Power Automate

 I've tried Vlookup and Index and Match, but cant figure out how to "print" respondents' names from Table1 Column D into cells D30:I68 in Table2 IF the respondent signed up for a particular day.

For Example: I want the names of everyone who signed up for 25-Nov to show up in cells in Row 30.

 

Additional Info:

People can sign up for anywhere from 1-38 days, so Day(s) in Row 2 don't equal the first day of the sign-up sheet (25-Nov).

 

When a Form response is submitted, every date that is selected by the respondent prints in Column E in Table1. Currently I am using Text to Columns to give each date its own cell 

 

 

 

 

excel
Formulas and Functions
  • dscheikey's avatar
    dscheikey
    Bronze Contributor
    Aug 19, 2022

    HiRBoyerr,  

    "I want the names of everyone who signed up for 25-Nov to show up in cells in Row 30."

    =TRANSPOSE(FILTER(Table1[Name],ISNUMBER(BYROW(Table1[[Day(s)1]:[ Day(s)38]],LAMBDA(array,XMATCH(C30,array))))))

    I did not understand what you wanted in addition.

    • RBoyerr's avatar
      RBoyerr
      Copper Contributor
      Aug 19, 2022

      HansVogelaar This is amazing! Thank you.

      It looks like you achieved this through a combination of TRANSPOSE and MMULT functions. Just so I can understand how you achieved this, could you explain in simple terms how your formula achieved my desired results, if not that's also cool-you've given me new areas to explore 

      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Aug 19, 2022

        RBoyerr 

         

        Table1[[Day(s)1]:[ Day(s)38]]=C30 returns an array of TRUE/FALSE values: TRUE for each cell in Table1[[Day(s)1]:[ Day(s)38]] that contains the date in C30 (25-Nov), FALSE otherwise.

        --(Table1[[Day(s)1]:[ Day(s)38]]=C30) converts TRUE to 1 and FALSE to 0:

        Each row corresponds to a student. We'd like to know which rows contain a 1.

        COLUMN(Table1[[Day(s)1]:[ Day(s)38]]) returns the column numbers of the Table1[[Day(s)1]:[ Day(s)38]])range:

        COLUMN(Table1[[Day(s)1]:[ Day(s)38]])^0 raises the column numbers to the power of 0 resulting in all 1s:

        Transposing results in a column with as many 1s as days1 -- days 38 has columns (i.e. 38):

        MMULT multiplies the array of 0s and 1s with this column. The result is a 1 for each row of the array that contains (at least) a 1, i.e. the student has subscribed to that date.

        This is used to filter the name column, resulting in a list of names for the date:

        Finally, TRANSPOSE converts this column to a row.

Resources