Grabbing only non-empty cells from a row range for every row

I have a survey that sends responses into a Google Sheet. The raw data that is inserted is messy, so I have another sheet that processes the incoming raw data.

One of the things I need to do is: from a range of columns in one row, pull the non-empty cells–and do this automatically with every new response that comes in.

This is what I’ve come up with that works with a single row, but I can’t figure out how to have it automatically apply to the entire column:

=transpose(QUERY(unique(transpose({'Raw Data'!AL3:AY3,'Raw Data'!AL3:AY3})),"SELECT Col1 WHERE Col1 <> '' ORDER BY Col1 ASC LIMIT 8",0))

Because the survey will insert rows, it ends up adjusting any cell references in my ‘Processed Data’ sheet, so I’ve been trying to use ARRAYFORMULA unsuccessfully.

Also, the range of columns will be more than 400 (options of schools), so manually writing out individual column names would probably be unfeasible.

Here’s a link to the spreadsheet

The column of issue starts on column O

Background: In the survey, a respondent can select multiple schools to a maximum of 8 out of a selection of 400+ schools. In the ‘Raw Data’ sheet, each school option is represented by a column.

Hopefully all of that made sense. Any help would be greatly greatly appreciated–I’ve been stuck on this for a while!

Compile cells when cell contains certain value

In Google Sheets, I have a range of values as follows:

enter image description here

I am looking for a formula that will search the range, find all of the cells that is a ‘wire’ (contains the term “wire”) and outputs the range to a compiled list of all of the wires in the A column list like this:

enter image description here

I could tell you what I’ve tried, but I haven’t even gotten close, so I feel it’s irrelevant.

Here is a copy of the example spreadsheet for copy-paste if needed:

Format cell if all the cells in a row are not empty

I tried all kind of formula but they all seem to work the other way around for me. I have a very large sheet with lot’s of columns. I’d like to have the first cell in the row to be a certain color if any of the cell in that row is empty. Basycaly a warning saying not all the cells have value.

First step, I’m trying to make it work just for one row for now and I’m stuck. Let say for row 2, format cell A2 in red if any of the cells in the range is empty. I tried
=len(join(“”, B2:ZZ2)) =$ B2=”” =NOT(ISBLANK(B2:ZZ2))

It’s not working! It’s getting red even if only one cell as something in it.

Use a range of cells as criteria in SUMIF

As an example, I have the below formula:


It is 3 SUMIFs all using the same criteria and sum range, the criteria uses 3 cells all in the same range.

I want to be able to condense this formula to something like the below:


where the criteria is the range of cells. I have tried:






Is there a way to achieve this ? perhaps even with SUMPRODUCT ?

Also, in place of the range E1:E3 I would like to use a named range, if possible, If not just a way of condensing the multiple SUMIFs will do for me.

How can I conditionally format cells to denote past/future/current dates?

I have a church rota Google Sheet. Column A shows the date of each service.

To make it more obvious at a glance, I would like cells in column A to apply formatting so that dates in the past are colored differently. Ideally, the next upcoming date would be highlighted (or at least dates in the current month or something like this).

I am somewhat familiar with conditional formatting but not using it on dates – is this feasible and if so how can I do it? In a perfect world, the entire row would be colored for past weeks but just the cell is sufficient if this is getting tricky.

How to create a unique list from a selected range of cells

I have a list in cells A1:A16 of foods and want to create a unique list with each item only appearing once in the list:

Eggs Bacon Hashbrowns Baked Beans Avocado Coffee Bread Bread Sandwhich meet Lettuce Tomato mustard mayo Crisps Cheese Crackers 

I have tried the formula =unique(transpose(split(ArrayFormula(concatenate(A1:A16&" "))," ")))

but this creates a unique list an splits words such as “baked beans” into new items like so:

Eggs Bacon Hashbrowns Baked Beans Avocado Coffee Bread Sandwhich meet Lettuce Tomato mustard mayo Crisps Cheese Crackers 

As you can see, “baked” and “beans” appears as two seperate items. How can I make it one item in the new unique list?

Ag-Grid Tree like structure attach cellClass to NodeChildren cells based on validation

In my Angular and angularjs application i have included Ag-Grid with columnDefs like below :-

 this.columnDefs = [   {     headerName: "Age",     field: "age",     cellRenderer: "agGroupCellRenderer"   },   {     headerName: "Name",     field: "name"   },   {     headerName: "Year",     field: "year"   },   {     headerName: "Country",     field: "country"   } ]; 

and my row Data is like below

 this.rowData = [   {     age: "Group A",     participants: [       {         age: "A.1",         name: "Michael Phelps",         year: "2008",         country: "United States"       },       {         name: "A.2",         age: "Michael Phelps",         year: "2008",         country: "United States"       },       {         name: "A.3",         age: "Michael Phelps",         year: "2008",         country: "United States"       }     ]}]; 

Now i want to attach cellClass to children grid values based on validation, like:-

 if(age< 23 || ''){   return['classNameThatiWantToAttach'];  } 

How to do this ??

You can make changes in below plunker also for this: –

Query returning results into merged cells in Google Sheets

Short Question:

I have a query function returning an array over merged cells. Instead of displaying all of the values in the array, some of the data is hidden in merged cells where only the first item is shown. I want to expand this array to cover more columns, so that all of the data is visible.

Long Explanation:

I have some data in columns A through E in a sheet labeled “Data”. In another sheet, “Query”, I’m trying to return that same data with a query, and condensed. I’ve put the formula =ARRAY_CONSTRAIN(QUERY({Data!A1:E},"Select *"),100,5) in Query!A1. (I know the query isn’t actually doing anything here; I’m trying to get this working before I add the ‘Where’ part)

This formula does not return the data, as it should, from the original sheet. Instead, I only see the first, third, and fifth columns. This is because in the sheet ‘Query’, columns A/B are merged horizontally, along with C/D and E/F. This causes the data which would have appeared in column B to be hidden. Basically, I need to force the array to be expanded into every other, non-merged, column; instead of returning data in A,B,C,D,E (of which I only see A,C,E), it needs to return data in A,C,E,G,I (so I see all of it).

Autopopulate a RANGE of Cells from one sheet (tab) to another in a single workbook

and thanks in advance! All I want to do is have cells in one sheet (tab) in a workbook auto-populate with data and formatting of a different sheet (tab) in the same workbook. I know how to do this one cell at a time, BUT is there a way to do this so that I can select a range of cells all at once? Example: Tab A, Range A3:D36 would automatically populate with data from Tab D, Range A2:D35.