2

Quick and dirty way : =UNIQUE(ARRAYFORMULA(TRANSPOSE(SPLIT(CONCATENATE(A1:F12&"🐥"),"🐥")))) (Source : organizing google-sheets data range into single column) However, care must be taken not to exceed 50k characters when using concatenate. If the case arises, simply break the formula into several pieces or use arrays. =unique({A1:A10;B1:B10;C1:C10})


2

There are multiple ways to do this : Query, Filter but I think SUMIF will suits the most : =SUMIF(arrayformula(regexmatch(A1:A10;C1));true;B1:B10) =SUM(FILTER(B1:B10;arrayformula(regexmatch(A1:A10;C1)))) =trim(REGEXREPLACE(CONCATENATE(QUERY(A1:B10;"SELECT sum(B) where A = '"&C1&"'"));"sum";)) Spreadsheet : A | B | C ---------------...


2

Notes are bound to the address of the cell and not to the content of the cell. A note is metadata to the cell. So, I am afraid you cannot just fix it. When a new response arrives, a new line is not added. It is just new content added after the latest content, leaving the exising content untouched. When you execute the query you "rearrange" the ...


2

Solution with a Formula : A1 : =ArrayFormula(if( mod(ROW(A1:A100),4)=1, text((round(ROW(A1:A100)/4)*7)+43730,"dddd dd mmm yy") ,) ) Solution with drag and drop Enter the dates in the cells : A1 is the first sunday, A5 is the second with this formula =A1+7 ...


2

Use curly brackets within this very simple query: =QUERY({Alpha!A2:B;Beta!A2:B;Charlie!A2:B},"select * where Col1 <>'' order by Col2") The trick here is the curly brackets {....} we use to create and combine the arrays from the different tabs. Extra tip: {Alpha!A2:B;Beta!A2:B;Charlie!A2:B} A semicolon ; combines vertically (in columns, one under ...


1

You should use a formula combining several functions so as to achieve what you need. The working formula is the following: =ArrayFormula(IFERROR (VLOOKUP(UNIQUE(Master!A1:A20), QUERY({Master!A1:M15},"Select * Order By Col13 Desc"), {1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13},FALSE))) EDIT In your comment you mention: Last ...


1

It looks like you are looking for a single formula solution but they could be hard to understand and maintain, specially if you aren't familiar on how the Google Sheets related features works. Related features Dates. Google Sheets handles dates as serialized numbers, where a day is one unit. Because of the above feature we could create a list of dates by ...


1

There is a widely used script for this: function sheetnames() { var out = new Array() var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets(); for (var i=0 ; i<sheets.length ; i++) out.push( [ sheets[i].getName() ] ) return out } Following that, you refer to it by placing =sheetnames() in a cell. PS: It is a very useful way when you ...


1

How to merge data into slides, is what I believe you are actually looking for. The overall steps are: Create your presentation exactly as you want it to appear using dummy content to help you with the design. For each content element that you'll be inserting, replace the dummy content with a tag. Tags are just text boxes or shapes with a ...


1

This formula takes a range containing strings or numbers and returns an output that has "T" prepended whenever an element occurs more than once. This is my understanding of the question: =IF(COUNTIF(A$2:A$11,A2)>1,"T"&A2,A2) If you want to have the input replaced itself automatically, you have to work with a script that triggers on edit.


1

The query you need is: =QUERY(Commissions!B1:O,"select B, count(C) where (B != '' and O = '"&B3&"') group by B order by count(C) label count(C)'Count'",1) Please notice how we reffer to the cell '"&B3&"' so as to include it in our query. I prepared for you a working sheet using cell B3. To the cell is added a drop-down validation for ...


1

There are three ways of matching Unicode characters according to Google Sheets' regular expression documentation: Using exactly two digit hex code: \xA0 Using up to three digits octal code: \240 Using any length of hex: \x{A0} or \x{0A0} or \x{0000000A0} etc. (any reasonable number of leading 0's is allowed apparently) Other ways of specifying characters ...


1

Use the formula =unique Day 1 Training: =UNIQUE(iferror( query('Hiring Tracker'!N:Q, "Select N Where O = '"&A2&"' and N >= date '"&G2&"' order by N asc" ,0 ) )) Day 2 Training: =UNIQUE(iferror( query('Hiring Tracker'!N:Q, "Select P Where Q = '"&A2&"' ...


1

Your formula is correct except you should be using straight double quotes (" ") instead of curly double (“ ”) quotes. This means your formula should be: =IF(D2 = "Mrs.Winehouse", "mwinehouse@aurorak12.org", IF(D2 = "Billie Eilish", "biellish@aurorak12.org", IF(D2 = "Jay Cutler", "jcutler@aurorak12.org",""))) Straight " " VS curly “ ” ...


1

To the right of column C create 2 new columns: D, F In cell D2 use this query: =QUERY(A2:C42,"select A,B where C <>'Omit'") Use these results for your pivot table. Link


1

The problem is that each formula that use your custom function triggers the execution of a new instance of the related script when the spreadsheet is recalculated. The obvious way to avoid to trigger so much script execution instances is to reduce the number of formulas that use the custom function, the way to get the required number of results is to make ...


1

There is a misunderstanding regarding the third parameter of getRange on var fillDownRange = ss.getRange(2424, 3, lr); as this parameter is the height of the range but what you expect is that it's the last row. To fix your code replace var fillDownRange = ss.getRange(2424, 3, lr); by var fillDownRange = ss.getRange(2424, 3, lr - 2424 + 1);


1

This has ben solved. I was targeting the wrong column, solution was: sales.getRange (2, 5, salesLR).insertCheckboxes(); sales.getRange (2, 6, salesLR).insertCheckboxes();


1

When dealing with dates in Google Sheets Query function, dates should be in a certain format. They need to be as a string literal in the format yyyy-mm-dd, otherwise it can’t perform the comparison filter. Query function needs dates in yyyy-mm-dd format In your case your formula should read 2019-09-13 =QUERY(FORMULARIO!A1:S, "SELECT G WHERE Q contains '...


1

The OP's spreadsheet shows they have solved the problem described in their question. The following is an answer to an un-asked question. The answer is in 2 parts: an alternative to the complex formula in column D (the image formula). an alternative to the code creating formula to populate Columns D, H and I (formula creation). Image Formula At ...


1

First highlight the range where you are going fill with data, including the row/cell with the initial data. Enter the initial data, (on Windows) press Shift+Enter (to get to the last row in the range) then press Ctrl+D. This will copy down all values by column.


1

I had the same issue and tried to solve it with Vidar's answer but because of deprecation it didn't work. The actual solution is the link by @hadi in the comment on Vidar's answer. "Vidar This is a great answer. I cleaned it up, and updated some of the deprecated methods, and got rid of unnecessary functions, and also modified it to be run from within the ...


Only top voted, non community-wiki answers of a minimum length are eligible