Forum Discussion
Having trouble printing names from one column into another table's rows If a condition is met
See the attached version. I corrected the Jan-1 and Jan-2 dates to be in 2023 instead of 2022.
See the attached version. I corrected the Jan-1 and Jan-2 dates to be in 2023 instead of 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
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.
- Thank you! This is a great breakdown, and really helped me understand the functions. Thanks again
I'll try to get back to you later, no time now.
