CODEWITHSUNDEEP
google-sheetsfilter
weizer
weizer

Reputation: 1117

FILTER and QUERY in google sheet

enter image description here

Hi everyone,

My goal is to calculate the number of students who choose Action 3 as one of their answer but the previous answer is not Action 2.

So based on the example in the screenshot above, Mary,Lary and yuki choose Action 3 as one of their answer, but only Lary having Action 2 as his previous answer. So the expected output in cell B2 is 2 since the previous answer for Mary before Action 3 is Action 5 instead of Action 2 and for yuki, the previous answer before Action 3 is Action 1 instead of Action 2.

May I know how to automate this formula? I have no idea how to start to build this formula.

This is my google sheet: https://docs.google.com/spreadsheets/d/1Zk7lr_2lEKEyY6qI0kNVXBHRF7mcsBJGDyR0BQjKyk0/edit#gid=0

Any help will be greatly appreciated!

Upvotes: 0

Views: 3695

Answers (2)

Erik Tyler
Erik Tyler

Reputation: 9345

Based on your replies to my questions in the comments section under the original post, I have added a new sheet ("Erik Help") to your sample spreadsheet, with the following formula in B2 (currently highlighted in bright green):

=ArrayFormula(SUM((C5:C="Action 3")*(IFERROR(VLOOKUP(ROW(A5:A)-1,{ROW(A5:A),C5:C},2,FALSE)<>"Action 2",0))*(IFERROR(VLOOKUP(ROW(A5:A)-1,{ROW(A5:A),B5:B},2,FALSE)=B5:B,0))))

ADDENDUM (after reading additional comments):

I've added a new sheet ("Erik Help 2") with the following formula:

=COUNTA(UNIQUE(FILTER(B5:B,C5:C="Action 3",VLOOKUP(ROW(A5:A)-1,{ROW(A5:A),B5:B&C5:C},2,FALSE)<>B5:B&"Action 2")))

Upvotes: 1

Aresvik
Aresvik

Reputation: 4620

Try:

=query({{offset(A4:C,1,0);"","",""},C4:C},"select count(Col1) where Col3 matches 'Action 3' and not Col4 matches 'Action 2' label count(Col1) '' ",1)

enter image description here

For same student, try:

=query({{query({A4:C},"where Col1 is not null order by Col2,Col1 offset 1",1);"","",""},query({A4:C},"where Col1 is not null order by Col2,Col1",1)},"select count(Col1) where Col2=Col5 and Col3 matches 'Action 3' and not Col6 matches 'Action 2' label count(Col1) '' ",1)

To see the results, rather than counting them:

=query({{query({A4:C},"where Col1 is not null order by Col2,Col1 offset 1",1);"","",""},query({A4:C},"where Col1 is not null order by Col2,Col1 label Col3 'Prev answer'",1)},"select * where Col2=Col5 and Col3 matches 'Action 3' and not Col6 matches 'Action 2' label count(Col1) '' ",1)

Upvotes: 1

Related Questions