Google Sheets – How to Return Transposed List of Strings in a Row

google sheets

In my Google Sheet, the range B2:2 contains mostly empty cells, but some cells contain strings. I would like to print these strings into a transposed list beginning at A12. This list should exclude all empty cells and should print (in consecutive cells) all the strings in B2:2.

So far, I've tried =ArrayFormula(INDEX($B$2:$E$2, 0, SMALL(IF(NOT(ISEMPTY($B$2:$E$2)), COLUMN($B$2:$E$2)), ROW(1:1)) as various websites and Stack Exchange answers have suggested, and =ArrayFormula(INDEX($B$2:$E$2, 0, SMALL(IF($B$2:$E$2<>"", COLUMN($B$2:$E$2)), ROW(1:1)). However, these formulas have only simply transposed the entire range beginning at A12 when the B2 is filled. When B2 is not filled, no values appear.

How should I proceed?

Best Answer

If I understood correctly, a combination of filter (which filters out the empty cells, keeping only the non-blank cells) and transpose, should work:

=transpose(filter(B2:2, len(B2:2)))

Related Solutions

Google-sheets – Tolerant Sum of column

Let me add my comment as an answer... :-)

You should be okay just using =SUM(), because sum() simply ignores non-numeric values.

Querying and Sorting Data from Multiple Sheets in Google Sheets

To sort a query you simply have to wrap it in SORT.

=SORT(
    QUERY(
        {SheetOne!C3:F;SheetTwo!C3:F;SheetThree!F4:G;SheetFour!F4:G}
    )
    , 1, TRUE)

For example, the above formula takes the data from the sheets inside the query, and then sorts by ascending order from column 1.

This also works well to solve the problem of row splitting, where some rows get split up if you use different queries to pull from rows you intend to keep uniform.

Best Answer

Related Solutions

Google-sheets – Tolerant Sum of column

Querying and Sorting Data from Multiple Sheets in Google Sheets

Related Topic