0

Introduction  (too long, don't read)

I have a data-arrangement problem. I'm now up to three qualifying files from coworkers, and for that matter, I've run into a couple of questions here on SE recently on which I cannot begin to offer help. The Sheets in question take a form where what I think of as "Records" will be arranged in a grid rather than rows. What I consider one or two sets of Related data is then aligned with the Records in the two available dimensions: columns and rows. It's such a natural approach that I cannot call it a "spreadsheet anti-pattern", but it does make the application of native functions arduous.

For example, I post one video per week to each of six channels. I want to plan for the rest of the year. Even just mentioning "52 weeks times 6 channels" prompts thoughts of a table arrangement. I know—I'll list weeks down the rows, the six channels across the columns, and fill my "schedule matrix" with video subjects. It is instantly understood by others. I can just add weeks as time passes. As I change plans, shifting already-scheduled videos around, swapping channels, etc. are trivial GUI operations. Oh, but when I scratch the surface, I'll see that I've broken everything else beautiful about spreadsheets.

The Goal

How can I take values arranged in a grid and have them reflected in a list, live, elsewhere?

In my sandbox Sheets file (view/comment or make a copy), I have coworker Reps arranged in the Teams and Focus roles they chose. It is sparsely populated, with 23 reps not shown as they have yet to sign up.

screenshot from linked Sheets file showing a grid of people, in columns of their focus, and rows of their team.

I have then manually rearranged everything in various ways off to the side. I wish to make one (or more) of those side lists automatically. At present, the only thing automatic is some Conditional Formatting. Here we see a list of the slots still vacant.

screenshot from linked Sheets file showing a simple list with a column for each of Team, Focus, and Rep.

My research has yielded little but bespoke or narrowly-applicable approaches. Or absurdly complex formulas and queries, so convoluted as to be unmaintainable. Plus this all gets much worse when they inevitably add another dimension of relationship; say… the videos should all have a title in another cell, and a link in another… or the PR Reps all need contact info listed. If only it was all done in proper tables from the start!

Without throwing in the Sheets-towel and migrating to a database or dumping their precious layout, are there clean, native spreadsheet ways to do this? To make ongoing use of a Sheet arranged as a record-per-cell Grid and dismantle it to record-per-row Table?

  • in your example sheet there are 5 examples and you want to turn columns A - H into one (all?) of those examples with formula? is that what you are after? – user0 Apr 12 at 18:21
  • Exactly. I need formulas that pull in A to H as source values and output any one of the example lists, like the pictured N to P. If I get more than one demo or method, great—but I'm not so focused on that because perhaps once I see a good "grid-to-table" approach, I'll be able to adapt it to whatever other listing I might need. – Joel Reid Apr 12 at 19:09
  • demo spreadsheet – user0 Apr 20 at 9:38
1

=ARRAYFORMULA(SUBSTITUTE(SPLIT(TRANSPOSE(QUERY(TRANSPOSE(SUBSTITUTE(
 TRANSPOSE(B2:H), " ", "♦")), , 999^99)), " "), "♦", " "))

0

1

=ARRAYFORMULA(SUBSTITUTE(SPLIT(TRANSPOSE(QUERY(TRANSPOSE(SUBSTITUTE(
 TRANSPOSE({B2:H2; IF(B3:H<>"", A3:A, )}), " ", "♦")), , 999^99)), " "), "♦", " "))

0

1

=ARRAYFORMULA(SPLIT(TRANSPOSE(SPLIT(TRANSPOSE(QUERY(TRANSPOSE(QUERY(TRANSPOSE(
 IF(B3:H<>"", "♦"&B3:H&"♠"&A3:A&"♠"&B2:H2, )), , 999^99)), , 999^99)), "♦")), "♠"))

0

0

=ARRAYFORMULA(SUBSTITUTE(IF(IFERROR(VLOOKUP(SORT(UNIQUE(
 TRANSPOSE(SPLIT(REPT(CONCATENATE(A3:A&CHAR(9)), 
 COUNTA(TRANSPOSE(B2:H2))), CHAR(9)))&" "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(TRANSPOSE(B2:H2)&CHAR(9), 
 COUNTA(A3:A))), CHAR(9)))), 1, 1),
 {TRANSPOSE(QUERY(TRANSPOSE(QUERY(SPLIT(TRANSPOSE(SPLIT(
 TRANSPOSE(QUERY(TRANSPOSE(QUERY(TRANSPOSE(
 IF(B3:H<>"", "♦"&A3:A&"♠"&B2:H2&"♠"&B3:H, )), , 999^99)), , 999^99)), "♦")), "♠"), 
 "select Col1,Col2")), , 999^99)),
 SPLIT(TRANSPOSE(SPLIT(TRANSPOSE(QUERY(TRANSPOSE(QUERY(TRANSPOSE(
 IF(B3:H<>"", "♦"&A3:A&"♠"&B2:H2&"♠"&B3:H, )), , 999^99)), , 999^99)), "♦")), "♠")}, 
 2, 0))="", SPLIT(SORT(UNIQUE(
 TRANSPOSE(SPLIT(REPT(CONCATENATE(A3:A&CHAR(9)), 
 COUNTA(TRANSPOSE(B2:H2))), CHAR(9)))&"♣"&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(TRANSPOSE(B2:H2&"♣♥")&CHAR(9), 
 COUNTA(A3:A))), CHAR(9)))), 1, 1), "♣"),
 IFERROR(VLOOKUP(SORT(UNIQUE(
 TRANSPOSE(SPLIT(REPT(CONCATENATE(A3:A&CHAR(9)), 
 COUNTA(TRANSPOSE(B2:H2))), CHAR(9)))&" "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(TRANSPOSE(B2:H2)&CHAR(9), 
 COUNTA(A3:A))), CHAR(9)))), 1, 1),
 {TRANSPOSE(QUERY(TRANSPOSE(QUERY(SPLIT(TRANSPOSE(SPLIT(
 TRANSPOSE(QUERY(TRANSPOSE(QUERY(TRANSPOSE(
 IF(B3:H<>"", "♦"&A3:A&"♠"&B2:H2&"♠"&B3:H, )), , 999^99)), , 999^99)), "♦")), "♠"), 
 "select Col1,Col2")), , 999^99)),
 SPLIT(TRANSPOSE(SPLIT(TRANSPOSE(QUERY(TRANSPOSE(QUERY(TRANSPOSE(
 IF(B3:H<>"", "♦"&A3:A&"♠"&B2:H2&"♠"&B3:H, )), , 999^99)), , 999^99)), "♦")), "♠")}, 
 {2, 3, 4}, 0))), "♥", ""))

0

0

=ARRAYFORMULA(QUERY(SUBSTITUTE(IF(IFERROR(VLOOKUP(SORT(UNIQUE(
 TRANSPOSE(SPLIT(REPT(CONCATENATE(A3:A&CHAR(9)), 
 COUNTA(TRANSPOSE(B2:H2))), CHAR(9)))&" "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(TRANSPOSE(B2:H2)&CHAR(9), 
 COUNTA(A3:A))), CHAR(9)))), 1, 1),
 {TRANSPOSE(QUERY(TRANSPOSE(QUERY(SPLIT(TRANSPOSE(SPLIT(
 TRANSPOSE(QUERY(TRANSPOSE(QUERY(TRANSPOSE(
 IF(B3:H<>"", "♦"&A3:A&"♠"&B2:H2&"♠"&B3:H, )), , 999^99)), , 999^99)), "♦")), "♠"), 
 "select Col1,Col2")), , 999^99)),
 SPLIT(TRANSPOSE(SPLIT(TRANSPOSE(QUERY(TRANSPOSE(QUERY(TRANSPOSE(
 IF(B3:H<>"", "♦"&A3:A&"♠"&B2:H2&"♠"&B3:H, )), , 999^99)), , 999^99)), "♦")), "♠")}, 
 2, 0))="", SPLIT(SORT(UNIQUE(
 TRANSPOSE(SPLIT(REPT(CONCATENATE(A3:A&CHAR(9)), 
 COUNTA(TRANSPOSE(B2:H2))), CHAR(9)))&"♣"&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(TRANSPOSE(B2:H2&"♣♥")&CHAR(9), 
 COUNTA(A3:A))), CHAR(9)))), 1, 1), "♣"),
 IFERROR(VLOOKUP(SORT(UNIQUE(
 TRANSPOSE(SPLIT(REPT(CONCATENATE(A3:A&CHAR(9)), 
 COUNTA(TRANSPOSE(B2:H2))), CHAR(9)))&" "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(TRANSPOSE(B2:H2)&CHAR(9), 
 COUNTA(A3:A))), CHAR(9)))), 1, 1),
 {TRANSPOSE(QUERY(TRANSPOSE(QUERY(SPLIT(TRANSPOSE(SPLIT(
 TRANSPOSE(QUERY(TRANSPOSE(QUERY(TRANSPOSE(
 IF(B3:H<>"", "♦"&A3:A&"♠"&B2:H2&"♠"&B3:H, )), , 999^99)), , 999^99)), "♦")), "♠"), 
 "select Col1,Col2")), , 999^99)),
 SPLIT(TRANSPOSE(SPLIT(TRANSPOSE(QUERY(TRANSPOSE(QUERY(TRANSPOSE(
 IF(B3:H<>"", "♦"&A3:A&"♠"&B2:H2&"♠"&B3:H, )), , 999^99)), , 999^99)), "♦")), "♠")}, 
 {2, 3, 4}, 0))), "♥", ""), "select Col2,Col1,Col3 order by Col2"))

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.