Search code examples
google-sheetsconcatenationarray-formulasgoogle-sheets-formulagoogle-sheets-query

Cell/column/row divider between formula/import result

This is a Google Sheets case, but if you good enough with MS Excel and know the solution, don't be shy and share it with the community. Your experience could be relevant to all of us.

Hello over there! I still didn't find any relevant solution in google, so I post my problem here.

I have a sample sheet and I import data via formula to cell via: =UNIQUE(INDIRECT(C$2&"!"&$O3) which means import all unique values via formula from Sheet!Range:range

It works fine, and I receive necessary data like: formula

But I want to see it like this:range space

with X (1,2,3,etc) row/cell/column spaces between them. I guess that =SPLIT formula should help me with that, but when I use , as a separator, I receive only first value, not the whole array of results that I needed it to.

So is there any way to achieve the result that I show at the picture above via formula or Google Script?

SAMPLE TEST SHEET with EDIT permission here: https://docs.google.com/spreadsheets/d/1eYeFI8nL39kLNDkcyyjeqV8tc9LwG7P7cn2NzUIB7Wo

Solution 
  • =ARRAYFORMULA(SUBSTITUTE(TRANSPOSE(SPLIT(QUERY(
 "♂"&UNIQUE(INDIRECT(F$2&"!"&$G2))&"♂♀",,999^99), "♂")), "♀", ""))

    0