This is an example of my data that is located on a sheet called Data:
| Date | Fruit 1 | Fruit 2 | Fruit 3 |
|---|---|---|---|
| 1/1/2020 | Apple | Mango | Apple |
| 2/1/2021 | Cherry | Banana | Pear |
| 2/1/2021 | Orange | ||
| 1/1/2022 | Apple | Cherry | Orange |
| 2/1/2022 | |||
| 2/1/2022 | Pear | Banana | |
| 21/1/2022 | Cherry | Banana |
Desired output:
Filtered to only show data between 1/1/2022 and 31/12/2022, sorted by the Count. The start and end dates are located in cells C2 and E2
| Suggestions | Count |
|---|---|
| Banana | 2 |
| Cherry | 2 |
| Apple | 1 |
| Orange | 1 |
| Pear | 1 |
I have achieved what I want in 2 steps:
First is a query that filters the data by dates specified and removes any rows that have no data in the 'Fruit 1' column. This formula is located in Cell D5.
=QUERY (Data!A:D,"SELECT A, B, C, D WHERE A > date '"&TEXT(C2,"yyyy-mm-dd")&"' and J <= date '"&TEXT(E2,"yyyy-mm-dd")&"' AND B<>''",1)
| D5 | E5 | F5 | G5 |
|---|---|---|---|
| 1/1/2022 | Apple | Cherry | Orange |
| 2/1/2022 | Pear | Banana | |
| 21/1/2022 | Cherry | Banana |
then running a second query on those results that combines the 'Fruit 1', 'Fruit 2' and 'Fruit 3' columns and returns the data sorted by count as shown in my desired output above.
=QUERY({E5:E;F5:F;G5:G}, "select Col1, count(Col1) where Col1<>'' group by Col1 order by count(Col1) desc label (Col1) 'Sorted List', count(Col1) 'Count'",1)
What I would really like to do is to do this in one query, but I can't find information on how to run a second query on the data that is generated by the first query... or how to approach it.
I hope this makes sense!