CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Notorious
Notorious

Reputation: 13

Merge column results of FILTER in google sheets

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:

https://i.sstatic.net/bs5T0.png

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.

Upvotes: 1

Views: 1609

Answers (2)

kirkg13
kirkg13

Reputation: 3010

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'.

Step 1

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.

Step 2

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.

Step 3

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)

enter image description here

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.

enter image description here

I hope that this is useful for some of you!

Upvotes: 1

marikamitsos
marikamitsos

Reputation: 10573

Please try the following

=ArrayFormula({"[217";N2:N&O2:O&P2:P})

enter image description here

(edit ranges to your needs)

Upvotes: 0

Related Questions