How do I join multi-row & multi-column data with an ARRAYFORMULA (or similar) in Google Sheets?

Question

I'm trying to construct a Google Sheets formula for a biologist and her students to help shovel data between programs.

It may be faster to look at my Google Sheet

The starting point:

image   x     y
-----------------
dog    994   557
dog    660   199
cat     74     9 
cat    105    23
cat   5296    93

The spreadsheet may have an arbitrary number of images and arbitrary number of (x,y) pairs. (The real image names are inscrutable-to-me shorthand for microscopy imagery.)

The output she needs is one line per image like so: imagename,x0,y0,x1,y1,x2,y2...

So, using the data above:

cat,74,9,105,23,5296,93   
dog,994,557,660,199

I think it'd be most helpful to have a single ARRAYFORULA-ish formula she could paste in A1 of a separate sheet.

After some thrashing around, the closest I've gotten is a two step approach. The 1st step uses a QUERY to create a column of unique image names. The 2nd step has to be pasted down an adjacent column, 1 per image, and looks like this:

=JOIN(", ", G3, ARRAYFORMULA(FILTER(B:B, A:A = G3)&", "&FILTER(C:C, A:A = G3)))

(where G3 is the unique image name, eg "dog")

It works, but is much less portable than a single formula for folks who may only the most basic spreadsheet experience.

Thanks!

user_5632629 · Answer

use in A1 on your new sheet:

=ARRAYFORMULA(REGEXREPLACE(TRIM(FLATTEN(QUERY(TRANSPOSE(QUERY(QUERY(
 IF(LEN(Sheet1!A2:A&Sheet1!B2:B&Sheet1!C2:C), 
 {Sheet1!A2:A&",", Sheet1!B2:B&", "&Sheet1!C2:C&",", ROW(Sheet1!A2:A)}, ), 
 "select Col1,max(Col2) 
  where Col1 is not null 
  group by Col1  
  pivot Col3"), 
 "offset 1", 0)),, 9^9))), ",$", ))

user_279806 · Answer

try:

=ARRAYFORMULA(
  TRANSPOSE(
    REGEXREPLACE(TRIM(
      QUERY(
        {
          TRANSPOSE(UNIQUE(FILTER(A2:A & ",", A2:A <> "")));
          IF(
               FLATTEN(FILTER({A2:A, A2:A}, A2:A <> ""))
            <> TRANSPOSE(UNIQUE(FILTER(A2:A, A2:A <> ""))),,
              FLATTEN(FILTER(B2:C & ",", A2:A <> ""))
          )
        },, 10^7
      )
    ), ",$",
    )
  )
)

How do I join multi-row & multi-column data with an ARRAYFORMULA (or similar) in Google Sheets?

2 Answers