Combining formula to get unqiue list from delimited values in a column with formula to get range concatenating Sheet-name to output

user_1304458 0
Viewed 43

I have cell values in a column that can be piped delimited ex: DR|Border|Yaque del Norte or empty or non-delimited ex: Cat

I have a formula that gets the unique list of all values (delimited on not) from a column

=SORT(UNIQUE(TRANSPOSE(ARRAYFORMULA(TRIM(SPLIT(JOIN("|",Elements!A2:A),"|"))))))

From the three values above I would get

Border
Cat
DR
Yaque

I have a formula that gets the range Elements!A2:A

indirect("Elements!"&substitute(address(1,match(A1,Elements!1:1,0),4),"1","")&"2:"&substitute(address(1,match(A1,Elements!1:1,0),4),"1",""))

But when I do this my output becomes

Elements!Border
Cat
Elements!DR
Elements!Yaque

All the delimited elements have Elements! added to them

How to prevent this

Thanks

Google sheet with data and formulas https://docs.google.com/spreadsheets/d/15tpuRdZUrSfiNapXWUtL2Zt1QOaxtf4T7SG1FwLyi30/edit?usp=sharing

google-sheets-formula google-sheets
1 Answers

Try the offset() function, like this:

=sort( unique( transpose( trim( 
  split( 
    join( 
      "|", 
      offset( Elements!A2:A, 0, match(A1, Elements!A1:D1, 0) - 1 ) 
    ), 
    "|", false, true 
  ) 
) ) ) )

See your sample spreadsheet, cell H2 in the "ignore" sheet.

user_130451930
Related