How to use FILTER to select only values repeated more than X times

Question

I am trying to use the filter function to select from 2 columns, only the values repeated. I will give you an example:

My column is like:

Names  Jobs  Value
-
Gerard  player  4
Gerard  player  2
Bill    player  5
Bill    painter 4
Bill    player  4
Bill    player  3
Gerard  painter 3
Helena  player  3
Helena  player  2
Mary    player  3

I have this Query

=QUERY(G2:I, "select G, AVG(I) where G is not null group by G")

And I can filter my data to get average for the values for each name. Okay, it's fine.

Now, I want for this Query, select only if I the name is repeated 3 times or more. I tried a lot of things with the Query, so, I deserved it.

Then, I tried to did it with my filter formula. It's like that:

=FILTER(B1:D,DASHBOARD!F24=C1:C)

I am using a dashboard to select the Job, to return the average, but the individual value for just one job, or when a lot of names are repeated 20 times, for example, it's not fair to show who just came one time.

So, to resume, I won't change my filter column (or query, if someone knows), to select the names only if the value is higher than X (maybe can be 5 or 10, idk).

I don't know if something is not clear, I am not good in English, but if u need, I can clarify anything.

Answer 1

Create a column which tests whether the name is repeated at least x amount of times. The formula in the 2nd row is

=COUNTIF(B:B,B2)>=$L$1

where the names are in column B, and L1 is where you have input the number of times the name should have been repeated. The data doesn't need to be sorted beforehand for this to work.

---

To use this column (let's call it K), you could create a new column J next to column I, and put

=IF(K2, I2, "" )

in cell J2, then copy down and query this new column J, as follows:

=QUERY(G2:J, "select G, AVG(J) where G is not null group by G")

There could be an alternative method for using column K, such as within the QUERY function itself.

Here is my example Sheet.

Answer 2

1. Sort your data by name (let the column for names be called B).

2. Create a column which tests whether the name is repeated at least three times. If the data has headings in row 1, the formula in the 4th row (for the third name) is

   =OR(B4=B2,B6=B4,B5=B3)

   This tests whether the name in B4 is equal to the name in two rows before it, two rows after it, or if the two adjacent names are equal. Since the data is sorted by name, the adjacent names shouldn't be equal if B4 isn't repeated.

   The above formula doesn't work for the first two rows (so you should fill those two cells in by hand), but you can copy this down to the rest of the rows.

---

To use this column (let's call it K), you could create a new column E next to the "Value" column and put

=IF(K4, D4, "" )

in cell E4, where K is the column where you put the OR test from point 2, then copy down and query this new column E.

There could be an alternative method for using column K, such as within the QUERY function itself.

Here is my example Sheet.

Answer 3

• all you need is this:

  =QUERY(QUERY({G2:I}, "where Col3 >= 3", 0), 
  "select Col1, avg(Col3) 
  group by Col1 
  label avg(Col3)''", 0)

  

• if you want 5 or 10 change the 3 in "where Col3 >= 3"