Transposing only comma separated values from an array in google sheet

Question

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:

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"), ", ")))

Can you include a sample input? I can only assume that your input is already in one column? The only problem is that some rows(in this case, cells since there is only one column) have multiple entries separated by commas. Did I understand your issue correcrly? — PatrickdC
– PatrickdC, Commented Oct 13, 2024 at 8:53
Have you tried using FLATTEN and then QUERY? =QUERY(FLATTEN(ARRAYFORMULA(SPLIT(<YOUR INPUT HERE>,","))),"SELECT Col1 WHERE Col1 IS NOT NULL") — PatrickdC
– PatrickdC, Commented Oct 13, 2024 at 9:14
@PatrickdC yes, I've started with FLATTEN then removed it, I didn't notice it influence result here. — Pavel_dp
– Pavel_dp, Commented Oct 13, 2024 at 18:34

doubleunary · Accepted Answer · 2024-10-13 06:01:40Z

2

Use tocol(), like this:

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

See tocol().

answered Oct 13, 2024 at 6:01

doubleunary

21.9k13 gold badges38 silver badges84 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Pavel_dp Over a year ago

Great thank you! That is exactly what I was looking for.

Collectives™ on Stack Overflow

Transposing only comma separated values from an array in google sheet

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related