Aug 19 2022 07:50 AM
Aug 19 2022 07:50 AM
PC/Windows 10 Pro
Excel Version 2207 (Build 15427.20210)
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.
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
Aug 19 2022 08:36 AMSolution
See the attached version. I corrected the Jan-1 and Jan-2 dates to be in 2023 instead of 2022.
Aug 19 2022 08:42 AM
"I want the names of everyone who signed up for 25-Nov to show up in cells in Row 30."
I did not understand what you wanted in addition.
Aug 19 2022 08:52 AM
@Hans Vogelaar 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
Aug 19 2022 09:01 AM
I'll try to get back to you later, no time now.
Aug 19 2022 01:04 PM
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.
Nov 03 2022 09:09 AM