Find numbers that repeat a certain number of times

I took a similar approach to SGIII, but with a couple of changes.

We both use some intermediate space to work out the frequency (Numbers don't have strong array functions to do this in one step). While SGIII put these calculations in adjacent columns, I opted for a separate table.

I took advantage of the fact that there a cap of 22 values to process.

To that end, I created a table with 23 rows + 2 footer rows (for the results area), and no header rows or columns.

(these row counts are important)

Column A1:A22 is filled with the formula:

=ROW()

which simply returns the row number, and is an easy way to build a list of consecutive numbers.

Column B1:B22 is filled down with the formula:

=COUNTIF(Table 1::A:B,A1)

This counts the number of entries in Table1::A:B (columns A and B of the values table) that match the current row's number.

Now I have a simple table that counts each of the possible numbers 1 through 22:

Once we're done, we can hide the 22 rows in the Table 2 since we don't need to see them, but they're there for clarity right now.

Then I added two footer rows to Table 2. These will be used to callout the entries with a frequency ≥4.

In cell A24, I set the formula to:

=LARGE(B,1)

this returns the largest number in the column. Similarly,A25 is set to:

=LARGE(B,2) which returns the second-largest value (used for the second place score).

You can add more if you need them, up to 5 footer rows.

Now we can see the quantities that occur 4 or more times, so it's time to work out what they actually are.

In cell B24, the formula is:

=IF(A$24≥4,XLOOKUP(LARGE(B,1),B,A,"",0,1),"")

decoding this, we first check to see if A$24 is ≥ 4. If it is, we perform a XLOOKUP() where we search for the LARGE()st number in column B and return the corresponding value from column A (which, if you recall, is the sequential counter). No matches, or if A$24 <4 returns an empty cell.

Repeating this in cell B25, just change the LARGE(B,1) to LARGE(B, 2) to get the second largest value, etc.

This gives me something like:

Where A24 shows me that we have some value with 5 occurrences, and A25 reports something with 4. B24/25 then perform the lookup to return the actual values we want.

Now, we can hide rows 1-22 and format the table such that it integrates with the main table:

Note, Table 2 has rows 1:22 hidden, and is positioned below the main table - you could even close the gap if you want it to look like it's part of the same table.

You can also set the display of cells A24 and A25 to hide the frequency value if you don't want to see it.

There will be a problem with this solution if there are two numbers that occur the same number of times. It will give the first number twice instead of giving both of them. For example, if 7 and 11 both occurred 5 times, it will give the results as 7 and 7.

The start of a solution that will not have that issue is to change the column B formula to

=COUNTIF(Table 1::A:B,A1) + ROW()/100

This will get the counts to be unique so that you can get the unique results. I think if you format the "frequency" cells to 0 decimals that might be the only other thing you need to do. Or other formulas could be used in those rows that wouldn't rely on cell formatting to look right.

Darn - forgot the case where there's a repeating quantity. Good catch.

The other way that might also work is a filter.

With my second table counting 1-22, you could set a filter on column B to show only cells that match 4 or greater. In this way the table dynamically resizes to show all rows with 4 or more. They're in numeric order, though, not size, if that matters.

Do you just need to quickly spot the high frequency numbers, or do you need to perform further calculations on them?

If you just need to spot them, then you could consider something like this, using Conditional Highlighting.

Add additional columns mirroring your original ones, and add this formula:

In C2, filled right and down:

=IF(COUNTIF($A:$B,A2)>=4,A2,"")

Then select the original cells and apply a Conditional Highlighting rule(Format > Cell tab) like this:

To enter the C2 in the Rule you'll need to first click the box-and-arrow icon, then select the cell in the table.

Use cell conditions in Numbers on Mac - Apple Support

COUNTIF - Apple Support

SG