Excel formula Filter multiple rows into a single row

Question

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!

Harun24hr · Answer 1 · 2022-07-06T02:26:38.590

1

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)

edited Jul 06 '22 at 02:26

answered Jul 06 '22 at 02:17

Harun24hr

Thanks a lot @Harun24hr but the formula comes out #NAME? result, I'm not sure if it is because I am using MAC excel. Anyway many thanks for your promptly response! – Diego Jordão Jul 06 '22 at 17:58
Mac excel doesn’t support Filterxml(). – Harun24hr Jul 06 '22 at 23:49

1 Answers1