Reputation: 11

Excel formula Filter multiple rows into a single row

I would like to know if is possible to filter multiple rows into a single row.

Something like combine all filters bellow in only one row with values in same order excluding empty cells:

=FILTER(A1:D1;A1:D1<>"";"NO RESULTS")

=FILTER(A2:D2;A2:D2<>"";"NO RESULTS")

=FILTER(A3:D3;A3:D3<>"";"NO RESULTS")

Thanks!

Upvotes: 1

Answers (1)

Reputation: 37125

FILTERXML() can give expected result.

=TRANSPOSE(FILTERXML("<t><s>"&TEXTJOIN("</s><s>",TRUE,A1:D3)&"</s></t>","//s"))

To know more about FILTERXML() read this by JvdV.

With beta channel of Excel-365 you can use TOROW() function.

=TOROW(A1:D3,1)

Upvotes: 1