Query to View only top counts and counted by group

Question

I wanted to use this query =query(G2:I80,"select G,H,I where H=max(H) group by I",-1)

From this:

Cdd Cts T

NWH 4 A1

LBB 3 A1

MP 3 A1

DC 2 A1

AK 10 A10

CC 3 A10

SC 2 A11

JL 1 A11

VT 1 A11

To This:

T Cdd Cts

A1 NWH 4

A10 AK 10

A11 SC 2

Am I doing it right? Or I have to come up with something to manually count them?

JPV · Accepted Answer · 2015-02-26T11:37:04.540

0

Try

=ArrayFormula(iferror(vlookup(unique(I2:I), sort({I2:I, G2:H},3,), {1,2,3},0)))

and see if that works for you ?

How does it work:

1: with sort() a new 'virtual' table is created with col I as the first column. That new table is sorted on col H (latest dates appear on top). 2: a list with the unique values is retrieved from col I (with the unique function) 3: that list is 'looked up' (with vlookup). When a match is found it retrieves the first 3 columns ({1, 2, 3} or: col I, G and H). Vlookup only returns the first match found: since our list is sorted, that will be the entry with the latest date. 4: if nothing is found an error is suppressed with the iferror().

edited Feb 26 '15 at 11:37

answered Feb 25 '15 at 10:15

JPV

26,499
4
33
48

But I have no idea what is going on in the formula. Haha. – cokenol Feb 26 '15 at 00:47
Hi Pang, I added a brief explanation to my answer. – JPV Feb 26 '15 at 07:17
Is there a way to lookup the top 2 voted ones in that team say if the top voted candidates are tied with equal number of votes? How do I work around from that? – cokenol Feb 27 '15 at 07:55

Query to View only top counts and counted by group

1 Answers1

Linked