Google-sheets – Get values that are repeated in a range two times or more in Google Sheets

google sheets

I have multiple columns of ticker names in the range A2:D like this:

A B C D
ticker1 ticker2 ticker3 ticker4
AAPL BA AMZN BA
BA DAL AMD F
COST F Z

The goal is to return cells that appear twice (in two different columns), and separately, to return cells if appears thrice (in three different columns), like this:

F G
repeated two times or more repeated three times or more
BA BA
F

I have a sample spreadsheet with sample columns and desired results.

I have tried the following formulas, but I cannot figure out how to search across all possibilities.

=IF(COUNTIF($B:$E, $A2:$A20)>0, $A2:$A20, ""): returns only one match from the first column

=VLOOKUP($A2:$A20, $B2:$E50, 1, true): returns only one match as well and the data isn't sorted. If I use false instead of true I get no results.

=QUERY($A2:$E50, "select A where A matches B:E label A 'Multis'", 0): only returns results if they are in the same row, i.e., when A3 matches C3, but not when A3 matches C4.

None of these formula can see if column B matches column C. If I could get any of these to work I could nest searches A→B:E, B→C:E, C→D:E and D→E, and then combine the results and delete repeats. I have not been able to get the A→B:E to work correctly.

Best Answer

If it does not really matter whether the repeated values are in different columns, you can do this with filter(countif()) and flatten() like this:

=unique( filter( flatten(A2:D4), countif(A2:D4, flatten(A2:D4)) >= 2 ) )

=unique( filter( flatten(A2:D4), countif(A2:D4, flatten(A2:D4)) >= 3 ) )

If you want the result be sorted, wrap these formulas in sort().

Related Topic