-1

I was trying to copy data from Sheet1 to Sheet2 using query. But I want to select MAX(A) and all the other columns where data exists (B,C,D...etc.) and group by B.

То clarify, in Sheet1 I have some similar rows which differ in col.A, which contains Timestamp, and a few other columns. I want to fetch only the latest record from each set of similar records.

So my Query is

=QUERY('Sheet1'!A3:S; "select MAX(A),B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S group by B")

It gives me error #Value! Unable to parse query string to FUNCTION query parameter 2 ADD_COL_TO_GROUP_BY_OR_AGG:C,...

Lyuboslav Alexandrov
  • 65
  • 8

2 Answers2

1

The other columns need to be in the 'group by' to. See if this works

=QUERY('Sheet1'!A2:S, "select MAX(A),B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S group by B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S")
JPV
  • 26,499
  • 4
  • 33
  • 48
  • Yes, but now it just copy the entire table data and no filter is applied, because I have diffences in other columns too. I want to group them only by A . – Lyuboslav Alexandrov Dec 13 '17 at 11:05
  • With query you can't select all those columns and then only group by B. Maybe you'll have to use an alternative method. Maybe use filter() instead? – JPV Dec 13 '17 at 12:37
0

I used this function =QUERY('Sheet1'!$A$1:S;"select MAX(A) group by B label MAX(A) '' ";2) to populate the first column in Sheet2 and then for each column this function =ArrayFormula(LOOKUP($A$3:$A;'Sheet1'!$A$3:$A;'Имот'!B3:B)).

Basically the second function searches for match from the cell in column A in the same table with a cell in column A in the first table, gives the result of the current column and all this copies itslef in the whole column.

After that I copied the formula manually for each column. And my problem is already solved.

Lyuboslav Alexandrov
  • 65
  • 8