New answers tagged

0

You have a cell containing exactly 45 comma-separated values. Your goal is to split those values, add a suffix for the row number, and then list the values in rows of three - that is, in 45 cells (3 columns wide, 15 rows deep). Use this formula: =ArrayFormula(TRIM(split(transpose(SPLIT(Query(transpose(split(REGEXREPLACE($C$2&",",",",&...


1

I believe what you're looking for is sumproduct. In the totals cell, you would use =sumproduct(multiplier_array, column_array)


0

Use this formula in cell D16 and copy down to cells D17 and D18. =join(", ", sort( query( { InStock!$C$2:$C,InStock!$B$2:$B; InStock!$D$2:$D,InStock!$B$2:$B; InStock!$E$2:$E,InStock!$B$2:$B; InStock!$F$2:$F,InStock!$B$2:$B; InStock!$G$2:$G,InStock!$B$2:$B; InStock!$H$2:$H,InStock!$B$2:$B; InStock!$I$2:$I,InStock!$B$2:$B; InStock!$J$...


0

Query-based Again, there are many ways to answer your question. Please consider this as one option for a solution. This answer is NOT perfect but I am including it because it opens up the scope to look at options for displaying your data. Props to @player0 for the answer to Comma separated list into matched columns pairings and infoinspired Split to Column ...


0

You want to break out the names of attendees for School, Restaurant and Park Locations. The resulting output should be a row for each attendee. Your script is intended to run as a custom function. You said that your script splits the names in column G, but column H & I it only keeps the first attendee. The reason that the names in the other Columns are ...


0

It's rather easy to get a list of data: just add the number of days as a parameter, e.g. =GOOGLEFINANCE(A1,"volume",A2,30) Specifying start and end date is also possible, see the documentation. If you want it for specific (non-consecutive) dates, I'd suggest to use VLOOKUP or some other query method. An example: The Finance formula is now =...


1

The problem occurs because your script parsing as string (toString)for all the values. You should add a condition to return date values as date or convert the resulting string to Date but the last is error prone, specially if you are not familiar with how Google Apps Script read and writes Date values. In your script instead of var cell = e.toString().split(&...


0

Say the dates are in column A starting at A2 and the scores are in column B starting at B2. Assume the data is not sorted in any way. In C2 enter: =sort(B2:B26,A2:A26,false) Column C are the scores sorted with the most recent at the top. In D2 enter: =sort(C2:C21,1,true) Column D are the most recent 20 scores sorted from lowest to highest. Finally in ...


3

You can use the following query formula: ={{"Name";QUERY({B1:B},"where Col1 is not null and not Col1 contains '@'")}, {"Email";QUERY({B1:B},"where Col1 contains '@'")}} Functions used: QUERY


1

In C1 enter: =index(B:B,2*row()-1) and copy downward. In D1 enter: =index(B:B,2*row()) and copy downward: If you need to enter the formulas below row#1, replace: row() with: rows($1:1)


0

You can use the following query formula =QUERY(A2:B6,"select B where A= ' "&C1&" ' ") Pro tip: To take it further. Notice the little triangle on the right side of cell C1? We can apply Data Validation in the word search cell with List of items using Custom,Kit,Basic (Please do adjust ranges to your needs) Functions used: QUERY


1

Please use the following rule =countif($A$2:$A2,A2)>1 (adjust range to your needs)


1

Instead of =QUERY(Data1!A2:C, Data2!A2:C, Data3!A2:C "SELECT * WHERE A "&E3&" '"&F3&"'") try =QUERY({Data1!A2:C; Data2!A2:C; Data3!A2:C}, "SELECT * WHERE Col1 "&E3&" '"&F3&"'") The above because QUERY doesn't allow multiple ranges but it could use an array.


1

I ended up using the following (where 'listaQuery' it's the range A2:F in the main sheet): =JOIN(CHAR(10), QUERY(listaQuery,"select B where C like '%"&A:A&"%' order by B")) this mostly solves the issue except that: It seems I have to add this code in a new cell every time a new tag comes up in the main sheet QUERY can't handle hyperlinks, so the ...


-2

Its simple, no need to complicate things with codes & formulas just Create a Form & from the form settings set the form to Collect the Users email address everytime the form is submitted- you just need to collate the data of eeach user the first time to link each users name to their email addresses incase they using personal email addresses-put the ...


0

You could use an empty parameter to not depend on the a blank cell instead of =if(sum(b1:b5)>1, "Yes", "") use =if(sum(b1:b5)>1, "Yes", ) Another common practice is to use IFERROR(1/0) instead of an empty parameter but this works because the same way because the second argument of IFERROR is empty. Related How to get a cell to show blank if it ...


1

It seems there is no literal for a blank/empty/null cell in Google Sheets. Using that as the third IF argument would be an elegant solution. Something else that works: designate a cell, say, A56, to be kept blank. The following formula would satisfy ISBLANK() (if the sum is 1 or lower): =if(sum(b1:b5)>1, "Yes", $A$56) The dollar signs aren't necessary ...


0

The following (untested) script is designed to be installed as an installable onEdit script. It will detect whether: 1 - a checkbox is marked as true, and 2 - the sheet is the form response sheet, and 3 - the checkbox is in Column F If all of those conditions are met, then an email will be sent. function wa14285401(e) { // this script is installed as ...


0

textjoin(" vs ", true, C10:C14) true means here that if cell is empty, then ignore


Top 50 recent answers are included