Google Sheets Query get Even/Odd rows in grouped results

Question

I have a long list of rows with dates on the side, and a text field after

01/01/2019 | ABC | ...

The list is ordered by date, and may have between 1 and 4 rows per date

01/01/2019 | ABC | ...
01/01/2019 | DEF | ...
05/01/2019 | ABC | ...
05/01/2019 | DEF | ...
05/01/2019 | ABC | ...
05/01/2019 | GHI | ...
10/01/2019 | ABC | ...
10/01/2019 | XYZ | ...

I can happily run a QUERY() which groups by the date and COUNT()s the number of rows matching that date

01/01/2019 | 2
05/01/2019 | 4 
10/01/2019 | 2

I'm trying to use a series of functions in acceptable Google Sheets format which will group the items by date, and then only return the Nth rows. I'm also happy with EVEN/ODD rows here.

Importantly, I don't want the EVEN/ODD based on the actual spreadsheet ROW(), but I need the EVEN/ODD/Nth based on the number of matching rows in the aggregated group, if that makes sense.

So I would like this output:

EVENS

01/01/2019 | DEF | (row 2 in group)
05/01/2019 | DEF | (row 2 in group) 
05/01/2019 | GHI | (row 4 in group) 
10/01/2019 | XYZ | (row 2 in group)

ODDS

01/01/2019 | ABC | (row 1 in group)
05/01/2019 | ABC | (row 1 in group) 
05/01/2019 | ABC | (row 3 in group) 
10/01/2019 | ABC | (row 1 in group)

Ultimately, my aim is to count all the occurrences of the text field (ABC/DEF/GHI/etc) that happen as the FIRST or SECOND or THIRD or FOURTH event for any particular day, then sort descending, but only include them (for example) if ABC was an EVEN row of that group, or if XYZ was an ODD row within that group (eg row 2 of the group, ignoring the fact in the whole spreadsheet it happens to be on row 35)

ABC | 156
DEF | 30 
GHI | 10
JKL | 8
MNO | 7
XYZ | 1

Tom Sharpe Tom Sharpe · Accepted Answer · 2019-08-08T12:25:06

You could do it with one formula if you wanted to

=filter(A2:B,ISEVEN(row(A2:A)-match(A2:A,A2:A,0)))

and

=filter(A2:B,isodd(row(A2:A)-match(A2:A,A2:A,0)+1))

assuming the data starts in row 2.

If the data started in a different row, you could do a lookup on the row:

=filter(A2:B,ISODD(row(A2:A)-vlookup(A2:A,{A2:A,row(A2:A)},2,false)))

and

=filter(A2:B,ISEVEN(row(A2:A)-vlookup(A2:A,{A2:A,row(A2:A)},2,false)))

Google Sheets Query get Even/Odd rows in grouped results

2 Answers