1

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.

  • What letter is the column for the names? – ahorn May 6 at 7:36
  • For Filter is B. – NumbersOfSix May 6 at 7:38
  • B to Names, C to jobs and D to value. – NumbersOfSix May 6 at 7:38
0

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.

  • Its works!!! Thank you man :) – NumbersOfSix May 6 at 9:54
0
  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.

  • Thanks a lot for coment Ahorn. I tried it, and i returned a FALSE value where its not duplicate. That helped me in the query: – NumbersOfSix May 6 at 8:27
  • I did it like this "=QUERY(G2:J, "select G, AVG(I) where G is not null and J =True group by G")" but, there isnt an alternative? I mean, i want to choose for example 3 or 5 or 10, depends of the value, and i cant without editing the formula with this method. – NumbersOfSix May 6 at 8:28
  • Sorry, I was under the impression you were querying the average of the values in column D. The method I described above should then be done next to column I. I.e. create a new column next to I where you type IF(K4, I4, ""), then the query is =QUERY(G2:J, "select G, AVG(J) where G is not null group by G"). – ahorn May 6 at 8:38
  • Np, probably my bad. But i have one question, how i can change the number of repeated? Sometimes i need chose only if its repeated 3 times, but sometimes its 5, and sometimes is 10, its possible? – NumbersOfSix May 6 at 8:44
  • This answer doesn't work if you want to change the number of repeats to test for. I have posted a better answer now. – ahorn May 6 at 9:03
0
  • all you need is this:

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

    0

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

  • Hey! First of all, thanks for your message! Maybe i was wrong, but what i mean, is choose the avg (as you did) depende of how many times the name is repeated, in this case, only gerard and bill need to apear, because i want to select exactly the names who works more than X times. – NumbersOfSix May 7 at 11:57
  • in that case, remove = from "where Col3 >= 3" – user0 May 7 at 11:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.