1

I have a table in wich raw data is entered manually, with info about clients and the requests they made.

enter image description here

And then I have this table in which I'm trying to analyze a bit the raw data (# of requests per client, time consumed, etc).

enter image description here

I need that in the last column (highlighte in yellow) to have the Type of document most type requested by Name, but I don't know how, what I have is the type of document most requested from ALL toghether, by using this formula:

=ARRAYFORMULA(INDEX(D$4:D$14,MATCH(MAX(COUNTIF(D$4:D$14,D$4:D$14)),COUNTIF(D$4:D$14,D$4:D$14),0)))

Also I need the analysis table to auto update each time a new entry is made in the raw data table.

Any help?

This is the sheet I'm using for testing:

https://docs.google.com/spreadsheets/d/1L5YDB-iPyLKgQwTYGJjZcjrOzpkM8fMMzMudJV-ukAA/edit

0

1 Answer 1

3

In N3 I entered this formula:

=ArrayFormula({query(query({A3:D,value(B3:B)}, "select Col1, sum(Col5), count(Col4), sum(Col3) where Col1<>'' group by Col1 format sum(Col5) 'h:mm:ss'"),"select* offset 1",0), vlookup(query(query(query(A4:D, "select D, A, sum(C) group by A, D"), "select max(Col3), Col2 group by Col2 label max(Col3)''"), "select Col1")&unique(sort(filter(A4:A, len(A4:A)))), {query(query(A4:D, "select sum(C), A, D  group by A, D"), "select Col1",0)&query(query(A4:D, "select sum(C), A, D group by A, D"), "select Col2",0),query(query(A4:D, "select sum(C), A, D  group by A, D"), "select  Col3",0)}, 2, 0)})

that seems to output the table you had as the expected output. Maybe someone comes up with something a little shorter (lol) but untill then.. make sure to test it thoroughly...

2
  • That's insane !! Jan 15, 2015 at 19:42
  • @JacobJanTuinstra, it is.. and I apologize to the OP for the length of the formula.. guess there's no way back from the query() road... :-)
    – JPV
    Jan 15, 2015 at 19:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.