All Questions
66 questions
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
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
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
2
votes
2
answers
69
views
Filtering Values Produced by an ARRAYFORMULA
I am using an ARRAYFORMULA to extract unique values from a list of names and total up a second column for each of those names.
Here is some example data and the ARRAYFORMULA I am using
Example Data
...
- 23
1
vote
1
answer
57
views
gSheet - Unable to get results using filter in an array
I'm trying to reference data that has multiple matches using an array formula.
It's not able to find any data. Yet if I change "DTR!C:C=B:B," to "DTR!C:C=B6, B7, etc," and use the ...
- 13
1
vote
2
answers
2k
views
Google Apps Script compare ranges to find matches
I have 2 Sheets "Client (Table1 below)" and "Color Description (table2 below)." I want to use GAS to do the following:
onEdit I want to compare 2 ranges then...
Find each instance ...
- 77
0
votes
1
answer
500
views
Google Sheets query not working when referencing a cell
I use the query function to extract data from my tables in google sheets. But one of the conditions must be a reference to another cell. I do it like this:
=QUERY({$A2:$B,ArrayFormula(Month($C$2:$C)),$...
- 77
1
vote
1
answer
161
views
Adding a header row between vertically joined queries (Or how to pad an array / create a fixed size array)
I am trying to make a google sheet that has a list of employees grouped by work area, where employees can be in multiple sections.
In our data structure we have a column G that is all the different ...
- 139
2
votes
1
answer
180
views
Arranging data by occurrences
Given a range with multiple items, I would like to count their occurrences and present the result as shown in this Google Spreadsheet sample using just one formula, if possible.
This is the closest ...
- 35
1
vote
1
answer
49
views
3 latest values, but only for a specific user
I am trying to get the average of the 3 latest values, but only for a specific user.
The range of C are the dates, the range of G are the values I am trying to find the average of, and the range of A ...
0
votes
1
answer
43
views
Maths based on dynamic groups
I am creating a sheet where data in column A will be input using a barcode scanner, and I want the sheet to recognize where one group of scans ends and the next begins, which will not be the same ...
- 115
1
vote
1
answer
2k
views
Query formula not working with error AVG_SUM_ONLY_NUMERIC
I am getting this error on my query.
Unable to parse query string for Function QUERY parameter 2: AVG_SUM_ONLY_NUMERIC
I don't understand whats the problem is here. actually, I have 2 columns in my ...
- 155
0
votes
1
answer
162
views
Google Sheet - How to deal with REPT() max size to do a FULL OUTER JOIN
In addition on my last question Google Sheet - How to FULL OUTER JOIN with one table?, I see REPT() function is limited with 32k characters.
How can we deal with it to make a FULL OUTER JOIN ?
In ...
- 33