Reputation: 53

Listing Non Contiguous Data Without Blanks Horizontally?

So I'm trying to combine cells in a particular row into a horizontal list. There is tons of literature and tutorials about listing non contiguous data without blanks vertically but I can't seem to find one for horizontal lists. The image displays the end result I would like. I don't need to transpose it.

Upvotes: 0

Answers (3)

basic

Reputation: 11968

You can use INDEX/AGGREGATE:

=IFERROR(INDEX(2:2,AGGREGATE(15,6,(1/(2:2<>""))*COLUMN(2:2),COLUMN(A1))),"")

Upvotes: 1

chris neilsen

Reputation: 53126

You can use TextJoin to create a XML string from the row, omitting blank cells. Then FilterXML to turn that string into a column range. Then Transpose it into a row. In Excel 20 you'll need to select enough cells to hold the result, and enter as an Array Formula (Ctrl-Shift-Enter)

Upvotes: 0

Gary's Student

Reputation: 96753

For Excel 365 ,in D6 enter:

=FILTER(2:2,2:2<>"")

Upvotes: 0

Listing Non Contiguous Data Without Blanks Horizontally?

Answers (3)

Related Questions