CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-sheets-formulagoogle-sheets-api
Elad Benda
Elad Benda

Reputation: 36654

Filtering compound columns in google sheet

I have these two tables in a sheet:

Given Table A and Table B I want to output Table C (values from Table A + You row with value for each date).

enter image description here

Is there any easy way to do so (I plan to use apps script as well)?

I have tried to filter two columns with this custom formula but it didn't work:

=QUERY(A:B; "where A='2021-01-25' and B='You|domain1'"; 0)

enter image description here

Upvotes: 0

Views: 119

Answers (1)

Krzysztof Dołęgowski
Krzysztof Dołęgowski

Reputation: 2660

I am not sure if I get your idea right and how long will be the dataset. So I tried to build an universal formula.

I assume that you have an indicator in your table that gives you idea who the winner is. I took 'value' from each day as this indicator.

First we need to find winners for each day:

=ArrayFormula(vlookup(E10:E12, sort($A$10:$C$19,1,1,3,0),{1,2,3},true))

This gives me:

2021-01-25 domain1 1.00%

2021-01-26 domain2 6.00%

2021-01-27 domain1 8.00%

Then I take source table and filter entries with "You"

=filter(A10:C19,B10:B19="You")

This gives me:

2021-01-25 You 5.00%

2021-01-26 You 7.00%

2021-01-27 You 10.00%

Final step is to combine these tables together and sort by date.

=sort({ArrayFormula(vlookup(E10:E12, sort($A$10:$C$19,1,1,3,0),{1,2,3},true));filter(A10:C19,B10:B19="You")},1,1)

My result looks like this:

2021-01-25 domain1 1.00%

2021-01-25 You 5.00%

2021-01-26 domain2 6.00%

2021-01-26 You 7.00%

2021-01-27 domain1 8.00%

2021-01-27 You 10.00%

My solution is available here: https://docs.google.com/spreadsheets/d/1gOPTmH8S9ir27wlk7BrXZJ3uthgC0xqwqMYiSf_MkqE/edit?usp=sharing

Upvotes: 1

Related Questions