-2

My Table looks like this enter image description here

This is a result of using =filter('Form Responses 1'!A:BJ,'Form Responses 1'!F:F=subjects!A19)

Now all I have to do is, ignore the blank cells and put in the last 3 columns.

I mean, columns N,O,P should come to H,I,J and similiarly other columns towards right of column G should come to columns to H,I,J.

Please help

player0
  • 124,011
  • 12
  • 67
  • 124
Shankar Rajagopal
  • 37
  • 5
  • 2
    Please be specific what is your expected output. Column Letters are not shown in the screenshot. Where do you want to filter from? – idfurw Aug 16 '21 at 07:00

2 Answers2

1

Change A4 to:

=filter('Form Responses 1'!A:G,'Form Responses 1'!F:F=subjects!A19)

Put this to H4:

=ArrayFormula(IFERROR(SPLIT(REGEXREPLACE(TRANSPOSE(QUERY(TRANSPOSE(filter('Form Responses 1'!H:BJ,'Form Responses 1'!F:F=subjects!A19)&"♦"),,ROWS(A4:A))),"^(♦ )+|(♦ )*♦$|(♦ )(♦ )+","$4"),"♦ ",FALSE),))

Sample: enter image description here

idfurw
  • 5,727
  • 2
  • 5
  • 18
1

use:

=FILTER({'Form Responses 1'!A:G, 
 TRIM(SPLIT(FLATTEN(QUERY(TRANSPOSE(
 IF('Form Responses 1'!H:BJ="",,'Form Responses 1'!H:BJ&"♥")),,9^9)), "♥"))}, 
 'Form Responses 1'!F:F=subjects!A19)
player0
  • 124,011
  • 12
  • 67
  • 124