0

I am creating a spreadsheet for homework completion. I want to search "empty space" and "incomplete" and add everything into one cell. everything is in one row.

I didn't do anything. I don't know how to start. I) would prefer a formula rather than a javascript.

For example: John Doe has homework "empty" and "incomplete". I want to find these entries, go back one column where the actual homework is and concatenate it at the end of the row for example "p 34, p 35, p 37".

as a bonus, I would like maybe to add a due date for these homeworks. The date is located two rows up and 4 columns to the left from "empty" and "incomplete" cells.

For example: "p 34 due date was: July 27, 2019, p 35 due date was: July 24, 2019, p 37 due date was: July 21, 2019".

player0
  • 124,011
  • 12
  • 67
  • 124
Kirill Soifer
  • 1

1 Answers1

0

try like this

=TEXTJOIN(", ", 1, QUERY({C8:D8;H8:I8;M8:N8;R8:S8;W8:X8;AB8:AC8}, 
 "select Col1 
  where Col1 is not null 
    and (Col2 = 'Incomplete' 
     or  Col2 = '')", 0))

0

player0
  • 124,011
  • 12
  • 67
  • 124
  • Thank you. It is great. Now I need to dynamically build a query range. (this part , C8:D8;H8:I8;M8:N8;R8:S8;W8:X8;AB8:AC8). Depending on the date, the range will expand. How can I do it? For example, if the date is sept 12 then the range should automatically change to C8:D8;H8:I8;M8:N8;R8:S8;W8:X8;AB8:AC8,AG8:AH8 – Kirill Soifer Jul 31 '19 at 07:15