Search code examples
google-sheetsformulaarray-formulas

How do I 1: Extract horizontal lines of data into vertical range with headings. 2: Vertical range of said data, show only selected items

I have a spreadsheet that I want to add data to as needed, and formulas to display it dynamically as needed.

Throughout, I'll refer to this spreadsheet which contains and example of my problem: https://docs.google.com/spreadsheets/d/1maHGRearacPc4TOqiMShvjdP0oskOVMXb_X9mD9Cc6k/edit?usp=sharing The spreadsheet is fully editable. So feel free to put your solutions in (though perhaps make duplicate pages so others card present their own solutions)

Problem 1: In the "Dynamic data" tab, I have a column with the names of an unknown number of groups (A2:A). Then to the right of each group name is it's data (B2:E). I want to transpose each row into a vertical column, adding text next to each title. In the example, F2 has the symbol I wish to add, G2 has where I would put the formula, and G2:G14 has the result I expect.

Notes:

  • Though there are currently 3 groups (A2:A4) in "Dynamic data" the idea is that I should be able to add groups as needed.
  • In the example, the data (B2:E) goes up to column E, but realistically the data would use many more columns.

UPDATE: Extra (optional): Any empty groups such as "Group 4" get skipped when creating array in G2.

Problem 2: In the next tab "Extracting Selected Data", I want to be able to select specific data (B2:B), and have another vertical range displaying that data, divided by the group titles (F2:F). This range would skip any groups that don't have data selected. (ie. E,F nor G are selected, so group 2 does not appear)

Extra (optional): B2:B has data validation, if possible, I'd like to only be able to select "non-titles" (ie. A,B,C,D,E...), but still have them visible in the data validation list.

UPDATED(No.2) Problem 3: If possible, having code which provides just the group headings that have the data selected, would also be amazing. It also means I can then create my own solution(s) to displaying data that I need.

Solution

  • Problem 1:

    Assuming the data starts from Column B

    =ArrayFormula(QUERY(FLATTEN(QUERY({F2&A2:A,B2:E},"select * where Col2 is not null",0)),"select Col1 where Col1 is not null",0))

    Problem 2: I think it can be done by a simpler regex or even only use regex.

    =TRANSPOSE(SPLIT(REGEXREPLACE(JOIN("♦",QUERY(A2:A,CONCATENATE("select A where A contains '------' or A='",TEXTJOIN("' or A='",TRUE,B2:B),"'"),0)),"(-----[^♦]+♦)(-|$)|(-----[^♦]+$)",),"♦"))

    Extra:

    You cannot set a list of values and limit the input with formula simultaneously.

    You may consider to use onEdit to reject input.

    Headings:

    =ArrayFormula(REGEXREPLACE(QUERY(TRANSPOSE(SPLIT(JOIN("♦",FILTER(A2:A,A2:A<>"")),"------ ",FALSE)),CONCATENATE("select Col1 where Col1 contains '♦",TEXTJOIN("' or Col1 contains '",TRUE,B2:B),"'"),0),"♦.+$",))

    =TRANSPOSE(SPLIT(REGEXREPLACE(TEXTJOIN("♦",TRUE,A2:A)&"♦",CONCATENATE("------ ([^♦]+♦)(([^-♦]+♦)*)((",TEXTJOIN("|",TRUE,B2:B),")♦)(([^-♦]+♦)*)|------ ([^♦]+)♦(([^-♦]+♦)+)"),"$1"),"♦"))