Group by name in query formula doesn't work

Warren Melloni

So I use a query function to count the number of instances where a specific name appears in column A of another sheet and display the result in column B of this sheet and the corresponding name in column A. Here is the function:

=ArrayFormula(QUERY(Attendance!A:A&{"",""},"select Col1, count(Col2) where Col1 != '' group by Col1 label count(Col2) 'Count'",1))

The problem is that while it mostly works, some of these names appear twice, for example Fred Jones appears as:

Col A | Col B

Fred Jones |  5

Fred Jones |  2

I've looked at the names and there's no discernible difference between them, and I don't see why it's not grouped. Is it possible to use wildcards or something to get Google to combine the names if the names are almost the same? As always thanks for any help.

player 0

try:

=ARRAYFORMULA(QUERY(TRIM({Attendance!A:A}), 
 "select Col1,count(Col1) 
  where Col1 is not null 
  group by Col1 
  label count(Col1)'Count'", 1))

Related

Group by name in query formula doesn't work

Warren Melloni So I use a query function to count the number of instances where a particular name appears in column A of another sheet and display the result in column B of this sheet and the corresponding name in column A. Here is the function: =ArrayFormula(

VBA IF formula doesn't work?

Mike - SMT I have some VBA code that works between some workbooks I'm having a problem with getting that IF formula. I'm not sure how I could go Subscript out of rangewrong. I have tried using .Range("O"), .Range("O:O")but .Range("O", i)both give the same 1004

Query/Import range formula doesn't work until page refresh

Naveen I have a sheet that uses the query and import range formulas given here =Query({Importrange("URL","'Trial'!A2:F"); Importrange("URL","'Trial'!A2:F")}, "Select Sum(Col6) Pivot ("&F1&")") It works fine, but whenever I change the Pivot reference in Cell

Why doesn't my Google app QUERY formula work?

gigabyte Here is the original query: QUERY(IMPORTRANGE("sheet_name", "Social Media Posts! A:AS"), "SELECT Col1, Col14, Col12, Col10, Col23, Col16, Col13, Col37, Col2, Col3 WHERE Col2 = 'instagram'") The output of the original query. Please check column H, whic

Spring + MongoDB tag @Query with $group doesn't work

Alam 94 NOTE: Scroll down to view edited messages. I am trying to mimic this query: db.sentiments.aggregate([ {"$group" : {_id:{theme_id:"$theme",sentiment_id:"$sentiment"}, count:{$sum:1}}}, {"$sort":{"_id.theme_id":1}} ]) Here is the code I generated to imi

=TEXT formula doesn't work

Nika Dolidze I am trying to use the formula =text(F4,"MMM") and it works for some fields as it shows the month but not for others, it only shows as "27/05/2016". Any ideas on why this is happening? I checked the format and it doesn't seem to be a problem. than

Group by database query in laravel doesn't work

Zeeshan Nisar I am new to Laravel. I have a database table named A with column names 1,2,3 . In column 3 I have different values like a,b,c. Now what I want is to print out the total value of a, b and c using the group by clause. Sorry, my English is no good.

Spring + MongoDB tag @Query with $group doesn't work

Alam 94 NOTE: Scroll down to view edited messages. I am trying to mimic this query: db.sentiments.aggregate([ {"$group" : {_id:{theme_id:"$theme",sentiment_id:"$sentiment"}, count:{$sum:1}}}, {"$sort":{"_id.theme_id":1}} ]) Here is the code I generated to imi

Query function doesn't work when I add WHERE to formula

austin farmer When I use the query function in Google Sheets , it works fine until I try to add a WHEREcondition . I only want to extract data that contains values in TRUEcolumn 10 . Right now, my query function pulls all the data, but when I try to add a WHER

Why doesn't this group by query work?

Joseph My query is not grouping correctly and returning wrong results, I can't figure out what the problem is. The query is as follows. FYI - the current form is unclear, the reason I need group by is because I removed all other parts of the query to find the

Group by name in query formula doesn't work

Warren Melloni So I use a query function to count the number of instances where a specific name appears in column A of another sheet and display the result in column B of this sheet and the corresponding name in column A. Here is the function: =ArrayFormula(QU

formula doesn't work

Rajesh S Using the following data my formula does not get the correct answer. The task is, that Sumall cells have text that starts with P. Use this formula: =SUM(IF(MID(A1:A4,3,1)="P",--SUBSTITUTE(A1:A4,MID(A1:A4,3,99),""))) The expected result is 20. notes:

Why doesn't this GROUP BY query work?

thin tube I am using the following query to query an Access table: SELECT (VIN&' '&Make&' '&Model&' '&CarYear&' '&ExColor&' '&InColor&' ')as CarDescript FROM TestTable WHERE (WorkOrderNumber='$workorder') GROUP BY AssignedEmp; But simi

Mysql doesn't work in query using GROUP BY - why?

Orange 555 Weird wording " Notdoesn't work!" I know, but the strange thing is that the returned results do exist user_iddespite the query pointing to NOT (user_id = 1). SELECT post_id, last_update FROM the_table WHERE last_update BETWEEN N

Why this (group) MySQL query doesn't work

Ilan Kleiman I have this MySQL query but can't seem to solve it. I want it to group the "symbols" in the table "portfolio". ( SELECT symbol FROM portfolio GROUP BY symbol) though, I would like it to limit its search to: ( WHERE key = '$key' AND hash = '$hash'

Formula doesn't work in VBA

January In the macro I recorded, I copied the formula from another workbook into the new workbook because I wanted the macro to insert that formula into a cell. Although the formula worked at the time, I couldn't use Marco because it would cause an error. I gu

Group by name in query formula doesn't work

Warren Melloni So I use a query function to count the number of instances where a specific name appears in column A of another sheet and display the result in column B of this sheet and the corresponding name in column A. Here is the function: =ArrayFormula(QU

Why doesn't my Google app QUERY formula work?

gigabyte Here is the original query: QUERY(IMPORTRANGE("sheet_name", "Social Media Posts! A:AS"), "SELECT Col1, Col14, Col12, Col10, Col23, Col16, Col13, Col37, Col2, Col3 WHERE Col2 = 'instagram'") The output of the original query. Please check column H, whic

VBA IF formula doesn't work?

Mike - SMT I have some VBA code that works between some workbooks I'm having a problem with getting that IF formula. I'm not sure how I could go Subscript out of rangewrong. I have tried using .Range("O"), .Range("O:O")but .Range("O", i)both give the same 1004

Query function doesn't work when I add WHERE to formula

austin farmer When I use the query function in Google Sheets , it works fine until I try to add a WHEREcondition . I only want to extract data that contains values in TRUEcolumn 10 . Right now, my query function pulls all the data, but when I try to add a WHER

Why doesn't my Google app QUERY formula work?

gigabyte Here is the original query: QUERY(IMPORTRANGE("sheet_name", "Social Media Posts! A:AS"), "SELECT Col1, Col14, Col12, Col10, Col23, Col16, Col13, Col37, Col2, Col3 WHERE Col2 = 'instagram'") The output of the original query. Please check column H, whic

formula doesn't work

Rajesh S Using the following data my formula does not get the correct answer. The task is, that Sumall cells have text that starts with P. Use this formula: =SUM(IF(MID(A1:A4,3,1)="P",--SUBSTITUTE(A1:A4,MID(A1:A4,3,99),""))) The expected result is 20. notes:

Why doesn't this group by query work?

Joseph My query is not grouping correctly and returning wrong results, I can't figure out what the problem is. The query is as follows. FYI - the current form is unclear, the reason I need group by is because I removed all other parts of the query to find the

Group by expression doesn't work in this SQL query?

shamveel_ahammed I am new to SQL. Can anyone help me find out why the "Group By" expression is not working in this sql query? i get this error ERROR at line 3: ORA-00979: not a GROUP BY expression The code I am using is CREATE OR REPLACE VIEW CUSTOMER_LINE_IT

Formula doesn't work in VBA

January In the macro I recorded, I copied the formula from another workbook into the new workbook because I wanted the macro to insert that formula into a cell. Although the formula worked at the time, I couldn't use Marco because it would cause an error. I gu

Formula doesn't work in VBA

January In the macro I recorded, I copied the formula from another workbook into the new workbook because I wanted the macro to insert that formula into a cell. Although the formula worked at the time, I couldn't use Marco because it would cause an error. I gu

Why this (group) MySQL query doesn't work

Ilan Kleiman I have this MySQL query but can't seem to solve it. I want it to group the "symbols" in the table "portfolio". ( SELECT symbol FROM portfolio GROUP BY symbol) though, I would like it to limit its search to: ( WHERE key = '$key' AND hash = '$hash'