All Questions
Tagged with google-query-language array-formulas
88 questions
1
vote
2
answers
164
views
Excluding Data in Googlesheet
I have a set of data. There is a case where I have to exclude some data from the data set. Here is the sample case:
https://docs.google.com/spreadsheets/d/1WbsNoUg9b29Hqex55H7fHqzW2rdgm2aAVgy-Y9DTa4Q/...
- 387
2
votes
2
answers
398
views
Google Sheets - transpose irregular column data in groups into rows
Much like the problem with the transposing of data in transpose column data I am stuck trying to transpose a set of data with multiple variables. The biggest issue I face is trying to remove useless ...
- 35
0
votes
1
answer
294
views
Virtually blank column in array?
I use this formula in Google Sheets:
=SORT({SORT({'Discord-D'!B2:B, 'Discord-D'!A2:A,
ARRAYFORMULA(IF('Discord-D'!G2:G = "", "", IF('Discord-D'!C2:C <> "", "...
user19120926
1
vote
2
answers
215
views
Exclude 0's from arrayformula
=ArrayFormula({"Test"; query(value(CTA!C2:C)*0.8)})
How would I exclude the rest of the 0's in empty cells?
- 135
1
vote
1
answer
439
views
ArrayFormula Only Populating When Not Empty
I have the following formula =ARRAY_CONSTRAIN(ARRAYFORMULA(IF(ARTIST_FILTER_MENU="", TRUE, IF(ARTIST_FILTER_MENU=Data!B2:B, TRUE, false))), COUNTA(Data!B2:B), 1) that will populate TRUE/...
- 359
1
vote
1
answer
45
views
Google Sheets OR statement is not performing an exact match
I have the formula below in a heading cell.
The problem is that any rows where BKW are in column B are also being seen as true. It is as if testing for B2:B="BRKW" is not an exact match.
...
- 162
0
votes
1
answer
90
views
If/Filter clause to query formula (with arrayformula embeded)
I have a response sheet on GSheets with over 100 columns/questions that can either be responded by Yes or No. If the candidate answers no they will have to fill in a freetext question.
The following ...
- 27
1
vote
2
answers
1k
views
Google Sheets, Array Formula to merge 2 Sheets into one, and append a new column to specify which sheet the data came from
I am trying to merge Sheet1 & Sheet2 into Sheet3. On Sheet3 I would like Column A to display which Sheet the data came from.
Sheet1 (Source #1)
A
B
John Doe
123 Street
Sheet2 (Source #2)
A
B
Jane ...
- 419
1
vote
1
answer
224
views
Search Contents of Multiple Rows For Specific Text Using Array Formula in Google Sheets
I have a column where each row contains multiple words, example below:
Fight; (People: Jack, Gil, Row, Pitt); (Link: Box, Wave, Line, Guard, Circle, Wide, Arrow,)
Fight; (People: Jack, Clive, Gareth); ...
- 37
1
vote
1
answer
820
views
Google Sheets - Find the lowest values in the entire table line by line
I can't find a way to make ArrayFormula and SMALL functions work together using the built-in Google sheets functions.
I have a table1 with items and their prices. Each position has the lowest price ...
1
vote
2
answers
1k
views
Array Formula For Maxifs
I feel like my question should be easy to figure out, but I've looked around and can't seem to find out how to get a basic array spill function that produces the max value. Here's my simplified data ...
- 51
1
vote
1
answer
132
views
Google Sheets Formula that finds employee with most hours worked for a specific location and shift (INDEX/MODE with multiple Criteria)
In Google Sheets, I need to find out which employee works more than 50% of the hours for a given shift at a given location based on timecard data (Table in col G:J). In the attached google sheet I ...
- 15
1
vote
1
answer
841
views
Multiple links to hyperlink string (Split and concatenate with ArrayFormula)
I'm trying to figure out how to make a formula to turn links in one cell into hyperlinks in another
From cell A1, A2 etc I'm trying to get cell B1, B2 etc using the Split and concatenate formulas. But ...
- 27
1
vote
1
answer
784
views
Query multiple google sheets in different sheets with sheet name in column1
I need help to show column 1 to get where sheet it came from, my current formula contains this:
=ARRAYFORMULA(QUERY({
{Product1!$AZ2:$AZ&"Product1",Product1!$A2:$AS};
{Product2!$AZ2:$AZ&...
- 47
2
votes
1
answer
17
views
Is there a way to make this query formula work? it was working before but I had to add more sheets and then it broke
Formula that combines all the sheets in my project and removes any empty cells
- 25