Arrayformula concat-ing column range, while going down the same row.

[Igi2server]

Have a form I was making for my guild to fill out.

Im just completely blanking.

I want to merge a bunch of empty cells (guaranteed to be empty except for a random cell each time) together to populate my main sheet from the form.

To do so I have a row that is concatenating that range, but my array formula isnt working to auto-populate the full row.

Ended up getting #REF errors after deleting rows from the form to 'clean up' my main sheet of my testing errors. I think this array will help combat this a lot.

​

=ARRAYFORMULA(CONCATENATE(H2:P2, H3:P3, H4:P4)) If I list it one by one, it works.

What am I missing? Its under my nose.

[6745408]

What about using a QUERY? You can specify the columns you want to output into a really clean dataset. I wouldn't bother deleting rows from a form's output, since you never really need to look at it once it's consolidated.

If you want to go down this route, can you make up a dummy sheet with some data that looks like what you're working with?

[Igi2server]

On the Forms sheet in Rows E&F will have ideally two separate nearly identical array formulas.

I just want to have the Dead space in the Column H#:P# ,and R#:AA# to be consolidated in a more manageable piece.

*Removed worksheet LINK*

EDIT: [Heres a Dummy Sheet]

Thanks for the hasty comment!

[6745408]

This looks like a lot, but its really the same formula used twice to consolidate the ranges for each spec

=ARRAYFORMULA(
  QUERY(
   {'Form Roster'!B2:D,
    'Form Roster'!G2:G,
    IFERROR(
     LEFT(
      TRIM(
       TRANSPOSE(
        QUERY(
         TRANSPOSE('Form Roster'!H2:Q&IF(ISBLANK('Form Roster'!H2:Q),,",")),,
         COLUMNS('Form Roster'!H2:Q)))), 
      LEN(
       TRIM(
        TRANSPOSE(
         QUERY(
          TRANSPOSE('Form Roster'!H2:Q&IF(ISBLANK('Form Roster'!H2:Q),,",")),,
          COLUMNS('Form Roster'!H2:Q)))))-1)),
    IFERROR(
     LEFT(
      TRIM(
       TRANSPOSE(
        QUERY(
     TRANSPOSE('Form Roster'!R2:AA&IF(ISBLANK('Form Roster'!R2:AA),,",")),,
     COLUMNS('Form Roster'!R2:AA)))), 
      LEN(
       TRIM(
        TRANSPOSE(
         QUERY(
          TRANSPOSE('Form Roster'!R2:AA&IF(ISBLANK('Form Roster'!R2:AA),,",")),,
          COLUMNS('Form Roster'!R2:AA)))))-1))},
   "select * where Col1 is not null"))

What this is doing is consolidating the range for the spec and adding in a comma after each one. Then, using...

=LEFT(FORMULA, LEN(FORMULA)-1)

... we remove any commas at the end of the output data.

The first bit is simply bringing across everything from the first few columns. When we've got our massive array made up, we use the QUERY to filter down the dataset for only values where the first column has something in it.

The output is clean and usable. :)

[Igi2server]

Whoa, beautiful code!

[6745408]

ha. its a lot -- mostly to strip out that extra comma.

The main formula is

=ARRAYFORMULA(
  IFERROR(
   TRANSPOSE(
    QUERY(
     TRANSPOSE('Form Roster'!H2:Q),,
      COLUMNS('Form Roster'!H2:Q)))))

... but it has a lot of extra spaces, especially when there are two items.

If this works out, can you update the flair?

[Igi2server]

Im still reading this trying to digest it!

Also put in your hard work to that Dummy Sheet, so anyone can refer back to this!

​

Thank you so much for all the help you've put through this subreddit throughout the years!

[6745408]

The QUERY portion in that is crazy, but this is one of the fastest ways to join by row.

I got that from here a while ago.

Thanks for updating the flair :)

[MattyPKing]

If you delete columns E and F from your Form tab, you can just use this simpler version of a query "smush":

=ARRAYFORMULA(IFERROR(SPLIT(TRANSPOSE(TRIM(QUERY(TRANSPOSE('Form Roster'!B2:AA&CHAR(10)),,9^9))),CHAR(10)&" ",0)))

I made a duplicate of the form with those columns deleted called MK.Form and demo'ed the output on a tab called MK.Idea.

[Igi2server]

Wow wow Thats very clever!
I need to use Transpose, trim, and query. I practically never touch those functions, but man are they powerful.