0

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.

1
  • Welcome to Web Applications. Please show what you tried directly in the question and add a brief description of your search efforts as is suggested in How to Ask.
    – Rubén
    May 21 at 20:11
0

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().

This site is temporarily in read only mode and not accepting new answers.

Not the answer you're looking for? Browse other questions tagged .