CODEWITHSUNDEEP
google-sheetssplitgoogle-sheets-formulatransposearray-formulas
David Patrick
David Patrick

Reputation: 13

Create Summary from Two Ranges

I have two ranges: one is a list of headers (Column A), and one is a list of categories (On a separate sheet in column B). Both are generated from other sources, and can be dynamic in length (i.e they cannot be guaranteed to be the same size).

I need to make a summary sheet from these. I want to take the first value of headers, then add all the categories, then the second value of headers, then all the categories, etc. Similar to:

HEADER 1
Role 1
Role 2
Role ...
HEADER 2
Role 1
Role 2
Role ...

And so on.

I've tried various options, and I currently have this: 

=ARRAYFORMULA( SPLIT(JOIN("|", A1:A6), "|") & "+" & JOIN("~", UNIQUE(Roles!B3:B)) )

This gets me one row with a column for each header with the entire roles range appended. For instance, column one has:

ON-SITE+Project Management & Creative Design~Production Staff~Video~Audio~

and so on across the sheet.

Ideally, I'd add two more SPLIT functions to separate this to a bunch of columns, then simply transpose into a single column and be done. However, it seems you only get one instance of SPLIT in an ARRAYFORMULA. When I add another SPLIT function:

=ARRAYFORMULA( SPLIT(SPLIT(JOIN("|", $A$1:$A$6), "|") & "+" & JOIN("~", UNIQUE(Roles!$B$4:$B)), "+") )

It simply splits the first column into two, then ignores the rest. If I add a second split to that, I only get the Header. It appears you only get one use of SPLIT inside ARRAYFORMULA, then it breaks down. I've read several things about how JOIN and SPLIT don't seem to play nicely inside ARRAYFORMULA.

Is there something I can add or order into this to make it work as desired? I'm also open to other methods, such as using QUERY or REGEX (those I know very little about REGEX). I attempted to create a literal array using TEXTJOIN and {}, but passing this via INDIRECT never seemed to work. I also need to solve this inside gSheets - no scripting unfortunately.

Editable Sheet Here

Upvotes: 1

Views: 84

Answers (1)

player0
player0

Reputation: 1

try:

=ARRAYFORMULA(TRANSPOSE(SPLIT(QUERY("♦"&TRANSPOSE(UNIQUE(Roles!C1:H1))&"♦"&
 TEXTJOIN("♦", 1, UNIQUE(Roles!B2:B)),,99^99), "♦")))

0

Upvotes: 2

Related Questions