Merge column results of FILTER in google sheets

Question

I have created a filter formula that scans a range to check for matches, like so:

=(FILTER(A7:Z500,A7:Z7="[217"))

which returns the following result:

What I now want to do is merge the resulting columns into one(the results of the & operand I think?). The result should be a single column that looks like this:

[217

Absent

Present

Absent

Thanks in advance.

It's basically one big sheet filled with activity entries. Each activity(Row) has some attendees(columns) and can either be blank(no data for that attendee) Present or Absent. In this case 217 is simply one of the columns/attendees. The IDs are not unique. — Notorious
Please share a copy of your sheet, with non-sensitive sample data, to make it easier for people to help you. — kirkg13
You mention a huge range A7:Z500, give a formula where data is unknown, show a minute image where merged cells are evident with no signs of columns or rows indications and a comment that sheds no light. Please share some data. — marikamitsos
Here is a link to sample data: docs.google.com/spreadsheets/d/… I cannot share the original because it has sensitive information. The filter is working as intended. I only want to use another function to merge the columns it produces. I don't see how the source data would be relevant, but that's what they look like... — Notorious

kirkg13 kirkg13 · Accepted Answer · 2020-11-03T15:08:49

This might work for you. Try the following formula, in the cell where you have your filter.

=ARRAYFORMULA(
  {"[217"; 
    TRIM(TRANSPOSE(
      QUERY(  TRANSPOSE(
        QUERY( TRANSPOSE(
          QUERY(
            TRANSPOSE(E1:J6),
            "where Col1 = '[217'",0)),
          "offset 1",0))
        ,,999)))})

If this works for you, I'll add some explanation. It's quite possible that I got confused, and have an extra QUERY(TRANSPOSE in there!

Here's a link to a working sheet, with a selection option for the value to filter on. https://docs.google.com/spreadsheets/d/1ZQ70HxGzoxep_pX6Olvhh4auWvEKDeW1Mi7zvpCPOiU/edit?usp=sharing

Very Detailed Explanation:

This explanation uses a cell, D1, to hold the criteria. So from the formula in my answer, where we compared to '[217' (hard-coded), this is now replaced by '" & D1 & "' (which is the same as '"&D1&"'. Do not have a space between the single and double quotes!)

The inner Query(Transpose selects the records matching the desired criteria, [217, and gives the following output. Note that the data had to be transposed first, to allow the query to search a column for the criteria, '[217'.

Then, a second QUERY(TRANSPOSE discards the header row, using OFFSET 1, leaving just the Present/Absent values. Note that this is the cells A10:F12 in the image above, but transposed, with the header "row" then discarded.

Next, another QUERY(TRANSPOSE is done. This is necessary to be able to force the values all into one row, using the trick of saying there are lots of header rows (the last value in the query function syntax). Normally this is a 1, for one header row, or 0 for none. Setting it to a huge number says treat all the rows as header data, and QUERY then merges every column up into one row, ignoring blank cells. I used 999, but often seen as 9^99, to ensure you cover a large data set of rows.

So, TRANSPOSE A10:C14 from the above image, and flatten the result into one row, and you get the following result.

Then we TRANSPOSE our array to change from a single row to a single column. And because you can end up with spaces around cell values from the previous QUERY step, we do a trim on the resulting array. Since the TRIM function opereates on a single cell or value, we wrap our formula in an ARRAYFORMULA, which causes the TRIM function to cycle through each value in the assigned range (our array of results so far)

And the very final step is to add back our criteria "header". We create an array - {D1,...} - with a semi-colon separator (in North America; check the syntax for your locale.) to force it as a column.

I hope that this is useful for some of you!

Merge column results of FILTER in google sheets

2 Answers