Need Google Sheets Array Formula To Return Just The Cell(s) > 1

Question

I'm looking to list duplicate values from another tab on my sheet. I have the formula below and it's almost complete. I need help with the formula only returning duplicate cells and how many occurrences.

Right now it will list out every cell in the column followed the number of occurrences in the next column to the right. I want it to show me just the cells where the number is greater than 1.

Any help would be appreciated!

=IFNA(ArrayFormula(
 {
    unique(filter(Completed!E2:E,Completed!E2:E<>"")),
    COUNTIF(filter(Completed!E2:E,Completed!E2:E<>""),unique(filter(Completed!E2:E,Completed!E2:E<>"")))
 }),"")

Here's an example of what it shows currently:

| 12345 | 1 |

| 01234 | 1 |

| 56789 | 2 |

Here's what I'd like it to return instead:

| 56789 | 2 |

player0 player0 · Accepted Answer · 2021-07-23T19:39:04

try:

=IFNA(QUERY(INDEX(
 {UNIQUE(FILTER(Completed!E2:E, Completed!E2:E<>"")),
 COUNTIF(FILTER(Completed!E2:E, Completed!E2:E<>""),
  UNIQUE(FILTER(Completed!E2:E, Completed!E2:E<>"")))}),
 "where Col2>1"))

or just:

=QUERY(QUERY(Completed!E2:E, 
 "select E,count(E) group by E"), "where Col2 > 1", 0)

Need Google Sheets Array Formula To Return Just The Cell(s) > 1

Here's what I'd like it to return instead:

1 Answers