2

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

the case above shows:

  1. there are two tables of data (city and country)
  2. I can filter each of the data according to a certain category
  3. I can combine both filter result manually by putting one above another

THE QUESTION:
is there any formula that can combine both filters (city and country) so that we got the expected result as shown in cell J2:J9??

player0
  • 124,011
  • 12
  • 67
  • 124
Randy Adikara
  • 357
  • 3
  • 10

1 Answers1

8

use:

={FILTER(A:A, B:B="yes"); 
  FILTER(D:D, E:E="yes")}

0

or:

=FILTER({A:A; D:D}, {B:B; E:E}="yes")

0

or:

=QUERY({A:B; D:E}, "select Col1 where Col2 = 'yes'")

0

player0
  • 124,011
  • 12
  • 67
  • 124
  • I am encountering super weird issues. I tested your solution with a small sample and it works fine, but when I am applying it to my big dataset, it only displays the first half (in this example the cities). Setting the data range only for the second half (Countries), it also works fine but not for both. Any idea? – Vincenzo Feb 06 '22 at 04:56
  • 1
    @VincenzoKöstler can you share a copy of your sheet? – player0 Feb 06 '22 at 13:03
  • 1
    definitely!! Thank you:) https://docs.google.com/spreadsheets/d/1wrTSLKLPdENPCKyfnovp4-Yr-dLKtPLVQn23LLJ2Y2k/edit?usp=sharing That is the file. Data is stored in "import" and the merging I am talking about is done in Sheet "Selection (Round 2)". – Vincenzo Feb 06 '22 at 14:07
  • 1
    works like a charm. how do you know this shit? – masbro Sep 15 '22 at 04:10
  • @masbro https://developers.google.com/chart/interactive/docs/querylanguage – player0 Sep 15 '22 at 08:58
  • @player0 not the QUERY , but the FILTER function with ; – masbro Sep 27 '22 at 03:56
  • 1
    @masbro see: https://stackoverflow.com/q/73767719/5632629 – player0 Sep 27 '22 at 11:56