I have a sheet that does auto-analysis of surveys, and I'd like to have it analyze subsets of the data based on another variable. I've got two separate tables: Table 1 is the variable I want to group the analysis by, and Table 2 has all the questions I want to analyze.
What I can't figure out is how to write syntax that will repeat the second table for each possible value of Table 1.
Input:
Table 1 Table 2:
Col1 ColAP ColAQ
A 21 43
B 4 45
C 5 47
Desired output
Col1 ColAP ColAQ
A 21 43
A 4 45
A 5 47
B 21 43
B 4 45
B 5 47
C 21 43
C 4 45
C 5 47
...
The code I use to get Table 2 array is as follows:
=SORT(
{
ARRAYFORMULA(
UNIQUE(
REGEXREPLACE(
FILTER(
{
Highlights!$AP$4:$AP,
ARRAYFORMULA(
REGEXREPLACE(Highlights!$AQ$4:$AQ," Total$","")
)
, REGEXREPLACE(Highlights!$D$4:$D,"^[\S\s]*$","Index")
}
,REGEXMATCH(Highlights!$D$4:$D,"^(.*)\:([Bb]efore|[Aa]fter).*")
)
,"^(.*)\:([Bb]efore|[Aa]fter).*","$1")
)
)
;ARRAYFORMULA(UNIQUE(REGEXREPLACE(FILTER({Highlights!$AP$4:$AP,ARRAYFORMULA(REGEXREPLACE(Highlights!$AQ$4:$AQ," Total$","")),Highlights!$D$4:$D},REGEXMATCH(Highlights!$D$4:$D,"^(.*)\:([Bb]efore|[Aa]fter).*")),"^(.*)\:([Bb]efore|[Aa]fter).*","$1")))}
,2,True,1,True)
I tried using variations of
=UNIQUE(Arrayformula({Col1},{Table2})), for every unique combination of values in Col1 and Table 2, but that didn't work because of unequal range lengths. or perhaps a query that would do that . . .
