Questions tagged [google-sheets-arrayformula]

Use for questions about the use of the Google Sheets built-in function ARRAYFORMULA.

Filter by
Sorted by
Tagged with
0
votes
1answer
23 views

Sort sentences based on formatting

I have a list of sentences in Google Sheets, each word in a different column. There is a stupid man. How we came here we don't know anything about. Finally, it has come to this. Shaking my head. How ...
1
vote
1answer
16 views

Issue with finding the averages of multiple rows at once

I am currently tasked with hundreds of rows of data for a project. I need to take the average of each row, but there are way too many rows for me to do by each individual row. Is there any way to take ...
0
votes
2answers
17 views

Using IF functions with formatting in Google Sheets

I'm working on a Google Sheet and want to reformat all instances where the number in a cell is "2" and the color of the cell is pink. Is it possible to use information about cell formatting (here, e.g....
1
vote
1answer
682 views

How to generate all combinations of letters in a word?

Using Google Sheets, how would I show all possible ways to type a word, for example: word Word WOrd WORd WORD wOrd woRd worD without any length word? The given word would go on Row A and the ...
-1
votes
1answer
162 views

Google Sheet Query returns first 3 rows in one row

I have made a simple query that combines data from 4 separate sheets: =query({'1.MA'!A13:O200;'2.PF'!A13:O200;'3.BM'!A13:O200;'4.C'!A13:O200},"Select * where Col2 is not null ") The objective is to ...
0
votes
1answer
68 views

Automatic merging of duplicate cells under common ID

I have data in column A with IDs and a column B with data for those IDs. Right now I have multiple (usually 2-3) rows with the same ID but different data. What formula to use to merge those into one ...
1
vote
1answer
951 views

Google Finance Historical Data with Multiple Attributes?

=GOOGLEFINANCE("NSE:"&A2, "all",(TODAY()-($N$2)),TODAY(),"DAILY") The above code returns OHLC data for specified days with the date column. What I want are only high, low and close columns. =...
1
vote
1answer
2k views

Repeat formula n number of times in Google Sheets

Apologies, I'm very new to Google Sheets and spreadsheets in general. I'm trying to fill some cells with repeating data, and then analyze it. The data is pulled from another large spreadsheet and has ...
1
vote
1answer
29 views

How to get a cell's parent ID in Google Sheets?

I'm trying to bulk add a bunch of building product categories to my site's MySQL database. There are about 650 entries and about 4 levels in the hierarchy. For it to work, each entry needs an ID, and ...
0
votes
1answer
43 views

Fetch value of a cell based on dropdown menu items results on another sheet

I have 2 Sheets named as Sheet1 and Sheet2... Sheet1: Contains Drop down list on B4 Column. Based on the selection of an item, it pulls the result of the open price shown on D4 column. Sheet2: ...
1
vote
1answer
13 views

Adding mm:ss times when expressed as TEXT

I have a series of numbers that, for a reason out of my control I can not change, are expressed as plain text strings on google sheets. The numbers are supposed to be minute second. mm:ss. For the ...
0
votes
1answer
17 views

Merging data sets from two tables with origin reference [duplicate]

In a Google Sheet, I have multiple tables containing a list of dates. I want to create a single (sorted) list containing all the dates and the table they came from. Table foo: 1.1.19 3.1.19 Table ...
1
vote
1answer
66 views

Filter and display duplicates in a table

Here's the link to my Google sheet: https://docs.google.com/spreadsheets/d/1SBD0_Ca0r_p3810u1ZNlbG6MXsvhFlLcwEJndVQJRP0/edit?usp=sharing The master data will show: And I would like to have a table ...
2
votes
2answers
60 views

How to make sequential list of months?

I want to fill column names with a sequential list of the last day of each month. For example, 3/31/2018, 4/30/2018, 5/31/2018, etc. Is there a formula that can do this? If cell B1 = 3/31/2018, I ...
0
votes
1answer
103 views

Importrange (or maybe another part of formula) behaves differently after a short time period

I am using this formula to look for data in column F related to data in column A between two spreadsheets. =ARRAYFORMULA(IFERROR(VLOOKUP(A2:A; {IMPORTRANGE("spreadsheetID";"sheet1!A4:A")\ IMPORTRANGE(...
1
vote
1answer
86 views

ArrayFormula stops working after shuffling rows

I have a pretty extensive sheet wherein many columns I use an array formula to automatically fill data in from various other spreadsheets. The problem I have is when I need to rearrange rows then what ...
1
vote
1answer
24 views

Dynamically update cell contents by merging incoming Google Form submissions

I have a google form where multiple students will anonymously submit comments/feedback for another peer. Currently I am using the formula below to essentially concatenate all of the student comments ...
1
vote
1answer
30 views

Comparing two cells and adding text to matching values

I have two columns, one with all items in a set and another with the items I currently have. I would like to compare those and for the matching results to have a bit of text added if they match. So ...
0
votes
1answer
21 views

Assigning rank in another sheet based on Average

I have a list of names with an average beside it for every day Monday-Friday. A subgroup of names within this list will be highlighted. I want a macro to assign ranks of the highlighted individuals ...
0
votes
2answers
495 views

Sum of LAST Column in Google Sheets

I'm wondering if there's a way to sum the last Column in a sheet. I add on data every month and have to re-do multiple formulas every time I do, as they're always based on the sum of the last column. ...
0
votes
1answer
29 views

Having a formula output to a different cell

I have a Google Sheet in the following format: ID | VAL ------------- 1 | dataH 2 | dataW 3 | dataX 4 | dataC 5 | dataG 6 | dataL ... | ... ID and VAL are on cells A1 and B1, ...
1
vote
1answer
106 views

IMPORTRANGE from a separate sheet based on closest date to today's date

Sheet1 will have data with different past dates Sheet2 - I want to import data from the 1st sheet and find the closest date to today's date. https://docs.google.com/spreadsheets/d/...
1
vote
1answer
2k views

Using ARRAYFORMULA and QUERY to produce results in multiple columns

I would like to be able to group rows based on unique values and then output the AVERAGE and STDEV values of two different variables for those groups. Using the table below as an example, I would ...
0
votes
1answer
92 views

Show blank cell result if a cell is empty

I am trying to create a social media keyphrase strategy using keywords. I would like to know if Google Sheets could return a blank cell if one of the cells in the concatenate formula is blank?
0
votes
1answer
26 views

How to convert an array of unique records into an array where records are repeated N times?

I have a Google sheet that records items and their quantities. In order to input them into our SalesForce system, I need to split each record into repeated rows, according to the quantity of the item. ...
1
vote
1answer
660 views

Google Sheets Conditional Formatting not working for all cells

This is my first question using StackExchange, so please bear with me. I am making a spreadsheet for tracking due dates for training. Sheet 1 shows due dates that people with view only capabilities ...
0
votes
1answer
12 views

Display results of success over attempts in X/Y format

I am tracking the number of successes out of the total number of attempts. So, for example, Column A would be the number of successes. Column B would be the number of fails. I would like Column C to ...
1
vote
1answer
10 views

Help using 2 parameters to output a specific category

I'm looking to be able to enter an "AGE" and a "V02" in a formula that tells me what "category" the person would be in ie; good, excellent, etc.
1
vote
1answer
30 views

Split data of a row in multiple rows

I've got some problem with data coming from a Google form. The result are: A|A|A|A|B|B|B|B|C|C|C|C| What I want to get is: A|A|A|A| B|B|B|B| C|C|C|C| Is there a way to get a similar result?
1
vote
1answer
20 views

Perform different functions depending on if a call is blank using Google Sheets

I want to determine the area of objects in each row. If values for length and width are present, I want to multiply the values to determine the area of the object. However, for circular objects, the ...
1
vote
1answer
161 views

How to do permutation of characters and print it horizontally along that row in Google Sheets?

I am stuck here in printing permutation of two or more characters horizontally along that row in which the characters are. Is it possible? Because I searched it all over and only found vertical ...
0
votes
1answer
18 views

How do I search a string, and return all values?

How do I use the script editor to search a string in one cell, and return all results from another sheet? For Example: I have my string in cell B1 - which is the item number Data to be searched in a ...
2
votes
1answer
47 views

Google Sheets sort question

I used to know this a few years ago and for the life of me can't figure it out and can't find an answer in Google help. I know how to sort columns, but only by the default A-Z format, how can I sort ...
1
vote
1answer
26 views

Full Join 2 lists with 3 key values

I'd like to fully join 2 tables on 3 key values, there doesn't need to be 0 value on blank cells if it makes it easier to understand (Some Array Formulas can be really confusing) I have income: I ...
-1
votes
1answer
16 views

Multiplying several numbers within a text string, is this possible?

I have cell A1 which is a text string: 100 blue cars, 100 red cars, 100 yellow cars In cell B1 I have a number which is a multiplier say 2. Is it possible to change the numbers within cell A1 ...
0
votes
3answers
356 views

Checkbox to activate a column in QUERY

Is there a way to activate/deactivate a column in QUERY function? I have a query to query and selection of several columns Select Col1,Col31,Col32,Col33,Col25,Col26, and want to have a checkbox for ...
1
vote
1answer
20 views

Looking for a way to get the current date represented as a numbered month with decimals

Essentially I'm hoping to find a formula that would return the current date as a month in decimal form. So if it's October 15th, the number returned would be 10.48. September 10th would come back as 9....
1
vote
1answer
22 views

Is it possible to add an IF condition to create a comment if a condition is met? [closed]

I have a sheet connected to a form and my users sometimes add cases many times which causes duplicates, I solved this by adding conditional coloring to the duplicated sheets. Now I want to know if ...
2
votes
2answers
805 views

Change default values when Google Forms autofills a Google Sheets

There's a Google form that's linked with a Google sheet: whenever a response is posted to the forme, a new line is added to the sheet (here's a quick tutorial just in case). Is it possible to have ...
0
votes
1answer
36 views

What function formula should I use to calculate my salary so that I don't have to differentiate between Friday and weekend manually?

These are the formulae I am currently using where I have to manually sort out if its Friday or weekend by using conditional formatting to bring attention to the minority that needs a different formula....
1
vote
1answer
49 views

Concatenating cell values to a matching cell

In the following sheet I am trying to grab a list of GUID keys from tab sheet1 and concatenate them to their respective order in tab sheet2. My expected output is in tab sheet2 column B as follows: ...
1
vote
1answer
136 views

Google Sheets conditional formatting for Max value that meets a second condition

I'm working with a baseball statistics spreadsheet in Google Sheets, and I want to highlight the maximum value in my batting average column, but only if that value is for a player who has satisfied ...
2
votes
2answers
49 views

Boolean test of whether a time is before or after midnight in Google Sheets?

I'm having a job trying to create a Google sheet which calculates the difference between the time I fell asleep and the time I woke up. Here's what I want to happen: Sleep time Wake time ...
2
votes
1answer
51 views

Comparing Unique Values to a Range

I have the following formula =CONCATENATE (FILTER(boxmod!AC2:AC, boxmod!I2:AB=concatboxmod!C2 )) The intention of this formula is to take a range and compare it to a list of unique identifiers on ...
1
vote
1answer
327 views

Left join 2 sheets on 2 key values, add constant value

I'd like to join by date on these 2 sheets (Income & Expense), on key values month and day and add constant value from FixedCost sheet, Income: Expense: Add FixedCost to every row/date: ...
1
vote
1answer
22 views

Link to a date cell in QUERY criteria

I have the following formula and it works fine =unique(query(ARRAY_CONSTRAIN(ARRAYFORMULA(QUERY( {Database!A1:AB, (Database!N1:N)-(Database!M1:M)}, "where Col9 <> '' AND Col1 >= date'2019-...
1
vote
1answer
35 views

Time not formatting as expected in Google Sheets

I imported a report to Google Sheets and the duration column shows 7 h 37 m or 14 h 5 m a similar format. When I replace h with : it still does not properly format to time duration and so I cannot ...
1
vote
1answer
321 views

SUMPRODUCT inside ArrayFormula

I have a fixed vector, say $A$1:$E$1 I also have a vector per row, say A2:E2, A3:E3, A4:E4... Now I want output in Column F as F2 = sumproduct(A1:E1,A2:E2) F3 = sumproduct(A1:E1,A3:E3) F4 = ...
1
vote
1answer
1k views

QUERY with Week Number

I use query to extract two columns from another sheet with data. Column A has dates Column B - numbers I want to extract only rows that correspond to a specific calendar week number. For example 19. ...
1
vote
1answer
19 views

How to count cell with upper leter in array?

I need to count the upper letter if exist in a particular cell. So my results should be as on the image below. important is ignore in count empty cell. I noticed that task is simple if I can use ...

1
3 4
5
6 7
11