How can I split this dataset by "|" but keep it in one column in Sheets?

April 27, 2022

I’m trying to use SPLIT() here, but it only gets the first row:
Notice that the ranges are obtained dynamically, so that moving columns around won’t lose the colum reference:

=ARRAYFORMULA(
INDIRECT("Sheet1!"&REGEXEXTRACT(ADDRESS(5,MATCH("PO #",Sheet1!$5:$5,0)),"[A-Z]+")&"5:"&REGEXEXTRACT(ADDRESS(5,MATCH("PO #",Sheet1!$5:$5,0)),"[A-Z]+"))&"|"&
INDIRECT("Sheet2!"&REGEXEXTRACT(ADDRESS(5,MATCH("PO #",Sheet2!$5:$5,0)),"[A-Z]+")&"5:"&REGEXEXTRACT(ADDRESS(5,MATCH("PO #",Sheet2!$5:$5,0)),"[A-Z]+")))

This generates, which is not the end result:

The idea is to split it by the | and stack them up and avoid blanks.

Here’s a sample of the data: https://docs.google.com/spreadsheets/d/1gh5w0czg2JuoA3i5wPu8_eOpC4Q4TXIRhmUrg53nKMU/edit?usp=sharing

Thank you!

>Solution :

use:

=ARRAYFORMULA(QUERY({
INDIRECT("Orders!"&REGEXEXTRACT(ADDRESS(6,MATCH("PO #",Orders!$5:$5,0)),"[A-Z]+")&"6:"&REGEXEXTRACT(ADDRESS(6,MATCH("PO #",Orders!$5:$5,0)),"[A-Z]+"));
INDIRECT("Scenarios!"&REGEXEXTRACT(ADDRESS(6,MATCH("PO #",Scenarios!$5:$5,0)),"[A-Z]+")&"6:"&REGEXEXTRACT(ADDRESS(6,MATCH("PO #",Scenarios!$5:$5,0)),"[A-Z]+"))}, "where Col1 is not null", ))

update:

=INDEX(QUERY({
 INDIRECT(   "Orders!"&ADDRESS(6, MATCH("PO #",    Orders!5:5, ))&":"&SUBSTITUTE(ADDRESS(2, MATCH("PO #",    Orders!5:5, ), 4), 2, ));
 INDIRECT("Scenarios!"&ADDRESS(6, MATCH("PO #", Scenarios!5:5, ))&":"&SUBSTITUTE(ADDRESS(2, MATCH("PO #", Scenarios!5:5, ), 4), 2, ))}, 
 "where Col1 is not null", ))