google-sheetstranspose

Transposing only comma separated values from an array in google sheet

I have a database with references in column V. Some cells have single value, like 0001, others have several comma separated values, like 0001, 0002, 0003. I am trying to retrieve this data to a single column, but can not deal with transpose.

Here's the formula which working but not transposing data:

=ARRAYFORMULA(SPLIT(QUERY(ShotLISTbase!A:V, "SELECT V WHERE A = '"&ShotLIST_Print!J1&"' AND V is not null"), ", "))

This formula returns comma separated data in different columns:

enter image description here

I need to put all results in first column, transposing comma separated values.

Thank you in advance.

I've tried IF condition, but didn't achieve wanted result:

=ARRAYFORMULA(IFERROR(IF(SEARCH(",", QUERY(ShotLISTbase!A:V, "SELECT V WHERE A = '"&ShotLIST_Print!J1&"' AND V is not null"))>1,TRANSPOSE(SPLIT(QUERY(ShotLISTbase!A:V, "SELECT V WHERE A = '"&ShotLIST_Print!J1&"' AND V is not null"), ", "))),SPLIT(QUERY(ShotLISTbase!A:V, "SELECT V WHERE A = '"&ShotLIST_Print!J1&"' AND V is not null"), ", ")))
Solution

  • Use tocol(), like this:

    =tocol(filter(split(ShotLISTbase!V1:V, ", "), ShotLISTbase!A1:A = ShotLIST_Print!J1), 3)

    See tocol().