The Stack Overflow podcast is back! Listen to an interview with our new CEO.

122 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
127 views

### How to remove duplicate words in a cell only if longer than a certain number of characters?

I need to remove a duplicate text in a cell but only if it is longer than 4 characters. I have this formula that only works for any types of duplicate words: =join(" ",unique(transpose(split(A1,", ")...
1k views

### Calculate Percentage with QUERY on Google Sheets

I have a Google Sheets database like this: Name | Value | Answer A | 100 | Y B | 200 | N C | 10 | Y A | 100 | N A | 1000 | Y B |...
359 views

### How do I reference the previous column of a calculated field on a pivot table? Looking to calculate percent change

I have a pivot table with a bunch of columns indicating the value over time, and would like to create a calculated field showing the change over each value. How would I reference the previous (and ...
27 views

### How to summary the multiple entry into a single sheet by name and by date?

Names are in rows and Dates are in columns. Sheet 1: Name 1-Jun-18 2-Jun-18 3-Jun-18....... KB CL KB EL AJ SL SD SL SD CL I need ...
766 views

### Aggregate results by week and month for charts

Is there a way to convert daily values into weekly averages for charting? Sample source sheet --------------------------------------------------- | Data 1 | 1/1/2018 | 1/2/2018 | 1/3/2018 | etc......
34 views

### Google Sheets - Query Multiple Columns for same Data

We have Employee training data in a Google 'Form Response' spreadsheet which ranges from A1 to JQ1000. This spreadsheet is automatically populated when a Google Form is completed but it is difficult ...
30 views

### Why does close price results have a date shift (one day off) while using GOOGLEFINANCE in a Query

I want to get last 40 days close price of stocks. I used below formula for the same but the result not showing the last day close price. =QUERY( GOOGLEFINANCE("nse:"&B2,"all",\$A\$2-80,81),...
32 views

### Visualize Data that updates dynamically

TLDR: How can I create a bar chart that updates the x-axis daily? The end goal is to have a chart that shows a side by side comparison of goal completions performance for each source of traffic. Here ...
29 views

### How can I change the formula of one cell based on the value in a different cell?

COLUMN D: Dropdown list of values COLUMN E: IF function returning different values depending on COLUMN D contents I have the functionality described above working properly. My problem is that I want ...
158 views

### Google Sheets Pulling data between a specific date and time, from a timestamp

I am trying to pull specific data from a timestamp that I will be using in several pivot tables. Here is my workbook https://docs.google.com/spreadsheets/d/1iYHYdATK06Wxl-Jtxe2P92Kh50XEsWgwx5d-...
13 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 ...
32 views

### How to get data to fill alternative cells when data is present in a zigzag pattern

https://docs.google.com/spreadsheets/d/1kwGbFd88cgifQRK_ZqsahQt1aDLzMTnmxHKs7mML5jA/edit?usp=sharing How to get data entered in one cell to fill another cell in a different location with same values ...
121 views

### Google Spreadsheets - Query Select A, B, MIN(C), group by A only

thanks for the help! Sorry if I'm expressing myself in a confusing manner, I'm a beginner at this... I'm trying to create a spreadsheet to help me plan and analyze grocery expenses more efficiently. ...
21 views

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

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 ...
686 views

### How to pull last value in a Google Sheets column and populate it across another worksheet column?

This is my first post here- I hope this is the right place for it, apologies in advance if it isn't. Context: I have a password protected presentation that I need to give users access to, but only ...
99 views

### How do I get the count number of weekdays in a datetime column in Google Sheets?

I have a timestamp in mm/dd/yy hh:mm:ss format from January to March. How do I count "how many days are Monday" in the range of A1:A199? (Or How Many are Tuesday, Wednesday, Saturday? This gives me a ...
49 views

### How to combine dynamic columns in Google Sheets while retaining data in other rows?

I have a Google Form that asks people to fill out their name, gender, and then their size preference for shirts and pants. The form automatically outputs the 2 sizes in separate columns. I want to ...
242 views

### How to concatenate cells using ARRAYFORMULA across rows and columns?

I have a document that's a recurring CSV import/export. I'm trying to concatenate images separated by ", " and using ARRAYFORMULA to make it all the way down. The problem is, I don't know how many ...
65 views

### Google Sheets' Query or Filter solution

I have a simple table with columns: A (Donor ID) B (Donation date) C (Donation Amount) I need to extract the following report: For each donor ID most recent donation date and amount donated ...
36 views

### How to join VLOOKUP results of multiple sheets into one cell

I am trying to use TEXTJOIN across multiple sheets, IF a cell is is the same I would like it to return the value from another cell in all sheets that are associated with it. In this example, I am ...
759 views

### Select last entry from spreedsheet

So I have a Google Form dumping data into spreadsheet tab form responses 1. I have created another tab labeled summary. I need to have the summary tab only show the latest entry for a given pilot ...
1k views

### Query Importrange with multiple values

This formula is working fine, but I need to add in other teams into Column A. I would need to return ATT SLC, ATT Orem, and ATT Scottsdale within this same range, but I'm not sure the language to use ...
699 views

I am trying to automate Worship list using Google Sheets, so it works good but one thing I can't do can be more than useful. I have a sheet like this: in row 1 dates and below songs used that Sunday....
55 views

### Pulling data from one sheet to another if

My knowledge is pretty basic when it comes to excel and Google Sheets. I'll explain what I'm trying to do. I'm trying to find a way to filter information from 1 sheet to another in Google Sheets. ...
13k views

### Google Sheets formula for “if contains” from range

I try to figure out how to get a value based on "if some word is in a range". So formulas from (Google Sheets formula for "if contains") works: =IF(ISTEXT(REGEXEXTRACT( A1, "sites")), 1,0) ...
2k views

### 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 ...
739 views

### Copying data from one cell in another based on date

I have a spreadsheet, using google sheets, that I have formed into the following metrics: Column A is the dates of the month Column B is the data I want to be able to copy the data from Column B ...
736 views

### In Google Sheets Query Max Date from Dates in Column A for a particular Project ID stored in Col B in Tab 'Data' from Tab 'Main'

Tab Data is as under Date_A Project_ID 01-May-18 EL-PR-18-08709-1 02-May-18 EL-PR-18-08709-1 01-May-18 EL-PR-18-08709-1 03-May-18 EL-PR-18-08709-1 02-May-18 EL-PR-18-08709-1 01-May-18 ...
215 views

### Is it possibile to concatenate with bidimensional arrayformula?

I have a table like this: +----------+----------+----------+----------+ | Photo1-1 | Photo1-2 | Photo1-3 | | +----------+----------+----------+----------+ | Photo2-1 | Photo2-2 | | ...
481 views

### Querying multiple ranges from form responses to new sheet

Recently, someone on this forum put together a formula for me. It works on a sheet pulling responses from a google form. I'm trying to model a new one after that but I'm having trouble. The working ...
32 views

### How to Iterate between colunns and rows?

I am running the following forumula: =IF(INDEX(C\$2:\$2, 1, ROW(1:1)),CONCATENATE(B\$2,"-",INDEX(C\$1:\$1, 1, ROW(1:1)))) On the following table Basically, If a value is present in column C2, I am ...
13 views

### Select All Columns Where Cell In Columns Contain A Word - Google Sheets

I am looking for a query formula that will allow me to select all columns (entire column) that contain a specific word within the column. You can see a test sheet here: https://docs.google.com/...
14 views

I am trying to use QUERY with importrange function to fetch custom data from another spreadsheet, it worked for a while but now it through loading data error consistently. if I refresh the sheet it ...
19 views

### In Google Sheets I need to find a string and return true/false, or even a count into a cell so I can perform an action

In my example i have a column with Names of signs we product in our sighshop and I need to be able to isolate similar named signs So say I need to return a a value if the cell is Bud Light and only ...
26 views

### Unable to filter rows in a sheet populated by QUERY

I am trying to filter one column A-Z to pair items listed in more than one selling app. However, if I apply an A-Z filter for a column with item codes, the sheet rearranges everything per the filter ...
32 views

### Import Form data into a sheet

I am trying to accomplish something that may not be possible. My work rents equipment to multiple companies and my boss wants me to setup a "database" of rental equipment and where its located and any ...
32 views

### Include filename of source spreadsheet

I have 2 spreadsheets for 2 employees and I combine them into 1. From employee 1: From employee 2: I use importrange to combine the sheets. I also know now how to get the spreadsheet file name by ...
43 views

### How to sort Column B to match with column A in Google Sheet?

I have 3 columns in Google Sheet. Column A have 95 rows with value. Column B and C have 500 rows with value: I need to sort Column B to match with Column A. Column C represent value associated with ...
30 views

### How to combine 3 sheets into one using QUERY(IMPORTRANGE)?

I am trying to combine 3 different sheets of data from range A2:O Col1, Col4, Col5, Col6, Col8, Col11, Col12, Col13, Col14, Col15 using Query({IMPORTRANGE(... function. I tried using the formula below ...
48 views

### Consolidate columns from a dynamic range into a single column in Google Sheets

How can Google Sheets take a range of multiple columns and rows and stack or consolidate all the non-empty cells into one column? For this case, I want non-unique cells. Also, each cell in the range ...
20 views

### How to filter after parsing CSV values contained in Google Sheets cells

I would like to be able to match all cells in column A (titles) that have one of the comma-separated values in column E (Key Search Terms). This will involve at least two steps. 1) Parse each of the ...
4 views

### Query to transpose data with col 2 offset for column headers

DATA: Col 1 = Job numbers Col 2 = Task numbers Col 3 = Date I would like to have: Col 1 = Job numbers All other Col = Dates with Col 2 as headers transposed So something like: query(DATA,"select * ...
25 views

### Pull data from another apreadsheet where Row and Column are strings

I am trying to pull data from a different sheet in google sheets by matching row and column header EG SheetB LOC prox dist qty sjc 12 15 19 dfw 10 16 8 atl ord In ...
23 views

### Import and SUM chosen (from drop-down) column to Sheet1 from Sheet2

What formula I can use in cell C2 to import one column from sheet 2 using a drop-down list? Here's the document.
23 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 ...
26 views

### Graphing two lines in Google Sheets based on values in first column

I'm trying to create two lines on the same graph, with one being 'Line A' based on Data labeled 'A' and 'Line B' based on B. The X-axis would be the date and would expand over time as more entries ...
416 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 ...
16 views

### How can I copy a row of data automatically to another spreadsheet when a condition is met in a drop-down menu?

Our small business uses a spreadsheet to track data from inquiries. Once booked, I need the data to automatically copy in the "Current Bookings" tab in the same sheet when "booked" is selected from a ...