CODEWITHSUNDEEP
filtergoogle-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-query
Jammo
Jammo

Reputation: 2172

Google Sheets Query get Even/Odd rows in grouped results

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

Upvotes: 2

Views: 2843

Answers (2)

Tom Sharpe
Tom Sharpe

Reputation: 34245

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.

enter image description here

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)))

Upvotes: 1

player0
player0

Reputation: 1

you can add helper column like:

=ARRAYFORMULA(IF(LEN(A1:A), COUNTIFS(B1:B, B1:B, ROW(B1:B), "<="&ROW(B1:B)), ))

0

and then filter for even and odd like:

=FILTER(A1:B, ISEVEN(C1:C))

=FILTER(A1:B, ISODD(C1:C))

0

Upvotes: 0

Related Questions