Hot answers tagged

42

Use the following formula to obtain the query you want. Formula =FILTER(A:A;(A:A="Combat Medic")+(A:A="Universal")) Explained In the reference it is explained by Ahab that + and * can be used as boolean expressions, as OR and AND. The solution with the IFERROR (see revision history) is of no use in this case, unless you want to find a particular string: ...


4

You should use the new Yahoo mail interface. It is easier to see your new options. Create a Folder Go to Options > Filters > Add Filter (then set up your filter to move to the folder)


4

Use a FILTER/MATCH combination. Example on your data below: =filter(C52:F57; match(C52:C57; I53:I54; 0))


4

Short answer Try =FILTER( 'Output Checker'!A:P, ROUNDDOWN('Output Checker'!A:A)=A2, 'Output Checker'!L:L <2, 'Output Checker'!M:M ="NO", 'Output Checker'!N:N ="Yes", 'Output Checker'!O:O ="NO" ) Explanation Google Sheets doesn't have a built-in function called CONTAINS. To evaluate that a date-time value met a date only criteria, round ...


3

On Locking Ranges using "$" The first problem I see is the $ sign is placed before the row. This tells the spreadsheet that it needs to LOCK that row (2 & 12) from changing numbers. The drag-handle operation relies on gsheets to find a pattern in your selection and repeat or continue it. If it doesn't find a pattern it repeats the elements you have ...


3

For a single phrase, you should wrap it in double quotation marks. "6 new updates and 2 invitations waiting for you on" If you have multiple exact phrases to test, use double quotes for each phrase, inserting a space between phrases to separate them (as opposed to a comma). "e-statement Notification Account Statement on" "08" 2009


2

You can use an application like Selective Tweets or TweetPo.st so your tweets ending with hashtag #fb will be automatically imported to Facebook, while all others will be ignored.


2

There is no way to block a video within YouTube. You can block a user though by navigating to the users profile and clicking block user. If you are looking specifically for a browser extension then please flag this question for migration to SuperUser. (Browser extensions are off-topic here as per the FAQ)


2

You may use an additional column to filter categories in place. Here's the formula to check if any text matches "golf" or "ski" : =REGEXMATCH(A2,"ski|golf") It will give True if cell A2 contains any word "golf" or "ski". If you want to check more then two words, the easy way is to write them into a separate list, say K1:K3: golf ski badminton and then ...


2

You might use INDIRECT within a query, say: =query(indirect($B4&"!D:H");"select sum(H) where D ='"&B$3&"' label sum(H) ''") but really you seem to have backed yourself into a corner. You appear to be trying to analyse data that has already been analysed. A more conventional solution may be to collect all the data together in one place (sheet) ...


1

delete everything in range D:Z paste to D2 cell: =UNIQUE(A2:A) paste to E2 cell: =ARRAYFORMULA(IF(LEN(D2:D); MMULT(IFERROR(LEN(G2:Z)/LEN(G2:Z); 0); TRANSPOSE(COLUMN(G2:Z2)^0)); )) paste to G2 cell: =TRANSPOSE(UNIQUE(FILTER(B$2:B; A$2:A=D2))) paste to G3 cell and drag down: =TRANSPOSE(UNIQUE(FILTER(FILTER(B$2:B; A$2:A=D3); NOT(COUNTIF(INDIRECT("G2:"&...


1

If you are not able to see filter triangle like in B1, select it by clicking on B1 and then simply press: Windows: CTRL + LEFT ALT + R Mac: CTRL + ⌘ + R


1

Assuming that the value of rev2_1 always will have 5 characters use =mid(A1,find("rev2_1",A1)+10,5) Explanation find finds the position of the first character of rev2_1 mid gets a text (substring) of certain length (5) from a starting position, in this case the result of find plus 10 characters.


1

short formulas: =REGEXEXTRACT(A1;"_1""\: ""(.{5})") =REGEXEXTRACT(A1;"_1""\: (.{7})") =REGEXEXTRACT(A1;"rev2_1""\: ""(.{5})") =REGEXEXTRACT(A1;"rev2_1""\: (.{7})") =REGEXEXTRACT(A1;"rev2_1""\: ""(.+)\"", ""rev2_2") =REGEXEXTRACT(A1;"rev2_1""\: (.+)\, ""rev2_2") =REGEXEXTRACT(A1;"_1""\: ""(.+)\"", ""rev2_2") =MID(A1;32;5) this will search for what is ...


1

There might be a shorter way, such as with string manipulation (e.g., MID) or regular expressions (REGEXEXTRACT), but this works: =1*trim(substitute(substitute(substitute(index(SPLIT(A1,":"),3),"rev2_2",""),CHAR(34),""),",",""))


1

This is not doable as you imagine it, because the only link/reference between C column and an array of A:B column is row order which upon filter change on the first sheet is lost. The solution would be to have all data in hidden columns of the first sheet and then call for them from the second sheet with =ARRAYFORMULA({Names!A:B,Names!D:D})


1

=ARRAYFORMULA(QUERY({QUERY({ TRANSPOSE(SPLIT(REPT(CONCATENATE(C2:C&CHAR(9)), COUNTA(D2:D)), CHAR(9))), TRANSPOSE(SPLIT(CONCATENATE(REPT(D2:D&CHAR(9), COUNTA(C2:C))), CHAR(9)))}, "where Col1 is not null", 0), ARRAYFORMULA(IF(QUERY({ TRANSPOSE(SPLIT(REPT(CONCATENATE(C2:C&CHAR(9)), COUNTA(D2:D)), CHAR(9))), TRANSPOSE(SPLIT(CONCATENATE(REPT(...


1

=FILTER(F:F, G:G="equals1", H:H<>"notequal2", (I:I="equals3") + (J:J="equals4"))


1

if your formula is: =FILTER(A2:B26, A2:A26 > 5, D2:D26 < 10) then just don't include the last number of closing range: =FILTER(A2:B, A2:A > 5, D2:D < 10)


1

put this for example in C1: =QUERY(A:A; "select A where A matches '.*[0-9]+.*'"; 0) to kill off zeros you can try this one: =QUERY(A:A; "select A where A matches '.*[1-9]+.*' and not A matches '.*[0]+.*'"; 0)


1

Your regular expression looks fine. That screenshot, however, looks like it is from your real-time reports. Real-time reports do not always respect filters, especially if those filters were recently created. The safer option is to allow data to collect for 12-24 hours and then inspect your content reports for the day. You should hopefully see the absence ...


1

Assuming dates in Column A and document names in Column B: =query(A:B,"select B, max(A) group by B") Reference QUERY function


1

It would seem from the description that you need all non-empty values of this column. In this case perhaps this formula would give you what you want: =UNIQUE('Sheet Name'!A$2:A) or if you want not unique, but non-blanks simply =FILTER('Sheet Name'!A$2:A, NOT(ISBLANK('Sheet Name'!A$2:A)))


1

You should move your 3 unique sport values to 3 additional columns: Ski Badminton Golf where you could filter any sport with values: true/false select Cell formula for the additional columns: =IF(IFERROR(FIND(E$3,$B4)),TRUE(),FALSE()) my example UPD: Second solution - Additional one column with unique left-column value and filter by search I've ...


1

I assume you want to get the values in K that are not appearing in L ? If so (and assuming your data starts on row 2), try: =filter(K2:K, isna(match(K2:K, L2:L,0))) If you only want to see unique values, then try: =unique(filter(K2:K, isna(match(K2:K, L2:L,0))))


1

I think your Q is basically "I want to auto-fill my progression by a set increment" and guess that that increment is 2 and you wish to start at 2, so put 2 in one cell, 4 in the cell immediately below, select the two cells, grab the fill handle and drag down to suit.


1

You can make the following your Google bookmark and/or homepage: https://www.google.com/webhp?tbs=qdr:y The reason for the link is that there's no way to default the search parameters for "Since"


1

if you're a firefox user, there's an add-on called youtube ratings preview. it shows the percentage of ratings on both search results and recommended videos on video page, but not on 'load more suggestions' results. not the ideal i guess, but it helps.


1

Google Reader's trends page itself should tell you all that you need. To head to the trends page from Google reader, the sidebar has the link to the trends page


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