Googlesheet Query Row Values in a Colunn by specific values in another

user_19684007 0
Viewed 29

Sheet contains table and 9 query problem set.

https://docs.google.com/spreadsheets/d/1-YW7prEz2rkCKangks2CeDK2spCidGQa8hJJhXWmGiI/edit?usp=sharing

Trying to solve for each customer request:

1 For Mike, into one cell, list Title and Rating for Title that is specifically "Where is the Sun?" List only those with Rental Date that is not TODAY(), which is 9/1/2022

2 For Lizzy, into one cell with results sorted by latest Rental Date, find all Rental Dates, Ratings, and Elements, specifically by her Favorite Title: Where is the Venus?

3 For Ed, his favorite element is specifically "A", query Rental Date, Source, Title, into one Cell AND/OR bring back Rental Date, Source, Title for rating that is exactly 10.00%

4 For John, find Source, Title, Rating into one cell, sorted by oldest Rental Date, that contain "A" in element but those elements can never contain "K"

5 For Mona, find the Rental Date, Title, and Elements for the lowest rating. In another cell, do the same but find for the highest rating. 6 For Claire, find the Title, Rental Date, and Elements for highest rated title that is "Where is Venus"

7 For Frank, find the Title, Rental Date, and Elements whose Rating is closest to the Average Rating. In another cell, do the same for the the Rating that is farthest from the Average Rating.

8 For Jack, find the Rental Date and Title whose Rating is the biggest outlier in the range of Rating?

9 For Mina, list all Titles that have duplicate Rental Dates, Group titles and state their duplicate duplate Rental Dates.

google-sheets-formula
1 Answers

your A2 cell is 9/1/2002 not 9/1/2022 (TODAY)

1:

=ARRAYFORMULA(TRIM(QUERY(CHAR(10)&FLATTEN(QUERY(TRANSPOSE(QUERY(A:E, "select C,D where C='Where is the Sun?' and A < date '"&TEXT(TODAY(), "e-m-d")&"'", 0)),,9^9)),,9^9)))

2:

=ARRAYFORMULA(TRIM(QUERY(CHAR(10)&FLATTEN(QUERY(TRANSPOSE(QUERY(A:E, "select A,D,E where C='Where is Venus?' order by A desc", 0)),,9^9)),,9^9)))

3:

=ARRAYFORMULA(TRIM(QUERY(CHAR(10)&FLATTEN(QUERY(TRANSPOSE(QUERY(A:E, "select A,B,C where E matches '.*(A,|, ?A$).*' and D = 0.1", 0)),,9^9)),,9^9)))

4:

=ARRAYFORMULA(TRIM(QUERY(CHAR(10)&FLATTEN(QUERY(TRANSPOSE(QUERY(A:E, "select B,C,D where E matches '.*(A,|, ?A$).*' and not E matches '.*(K,|, ?K$).*' order by A desc", 0)),,9^9)),,9^9)))

5:

=ARRAYFORMULA(TRIM({QUERY(CHAR(10)&FLATTEN(QUERY(TRANSPOSE(QUERY(A:E, "select A,C,E where D ="&MIN(D2:D), 0)),,9^9)),,9^9), QUERY(CHAR(10)&FLATTEN(QUERY(TRANSPOSE(QUERY(A:E, "select A,C,E where D ="&MAX(D2:D), 0)),,9^9)),,9^9)}))

6:

=ARRAYFORMULA(TRIM(QUERY(CHAR(10)&FLATTEN(QUERY(TRANSPOSE(QUERY(A:E, "select C,A,E where C = 'Where is Venus?' order by D desc limit 1", 0)),,9^9)),,9^9)))

7:

=ARRAYFORMULA(TRIM({QUERY(CHAR(10)&FLATTEN(QUERY(TRANSPOSE(QUERY({A2:E, ABS(D2:D-AVERAGE(D2:D))&""}, "select Col3,Col1,Col5 where Col6 ='"&MIN(ABS(D2:D-AVERAGE(D2:D)))&"'", 0)),,9^9)),,9^9), QUERY(CHAR(10)&FLATTEN(QUERY(TRANSPOSE(QUERY({A2:E, ABS(D2:D-AVERAGE(D2:D))&""}, "select Col3,Col1,Col5 where Col6 ='"&MAX(ABS(D2:D-AVERAGE(D2:D)))&"'", 0)),,9^9)),,9^9)}))

8:

no idea what "biggest outlier in the range of Rating" is. if you want that negative value just say so...

9:

kindly provide an example of the desired result in your sample sheet

user_56326290
Related