Find common second films among voters sharing a first choice

Hello zedcurrante, and welcome to the Apple Support Community.

From your example, it sounds like you’re trying to identify which titles most frequently appear alongside a selected film across all voters’ lists. Apple Numbers doesn’t include a built-in feature for this type of co-occurrence analysis, but it can be done using formulas, a Pivot Table, or AppleScript, depending on how you’d like to work with your data.

If you’re using Apple Numbers, a practical approach is to:

1. Filter the table to show only voters who selected the film you’re interested in.
2. Create a summary (or Pivot Table) of all the other titles from those same voters.
3. Count how many times each title appears, then sort the results from highest to lowest to identify the most common companion films.

For larger datasets (such as 1,600 voters), using a Pivot Table or AppleScript will be much more efficient than doing this manually.

Helpful links:

Numbers User Guide for Mac - Apple Support

Numbers User Guide for Mac - Apple Support

Could you let us know whether your data is stored in Apple Numbers, Microsoft Excel, or another spreadsheet application?

Also, are you looking for a formula-only solution, or would an AppleScript or shortcut-based approach be acceptable?

Please let us know the outcome.

Your feedback may also help other members of the Apple Support Community working with similar datasets.

🙏🏻

Have a great day.

And one more thing…

Never forget to think different.



Following on from the previous example we worked through... there's a gap in what you're asking...

> My goal is to be able to search a for a film and then see the second movie that is common in their lists.

Let's say you want to search for "A Clockwork Orange"... are you saying you want to find people whose first choice is A Clockwork Orange, then display the second choices? or do you want anyone that has A Clockwork Orange anywhere in their choices... a subtle, but important difference.

> The survey does not have the respondents rank their selections—they chose their top ten without considering one choice over the other.

Glad I checked, since my instinct was that the selections were ranked :)

This is another multi-step sequence that would benefit from some helper tables/columns.

To break down the logic, given the name of a film, you need to:

Generate a list of people who selected that film.

Generate a list of all films selected by those people

Filter the list to count duplicates.

Sort the counted list to find the most popular films:

I'll start by breaking this out step-by-step using a dummy table to show each step, then show how you can compress the process into a single step. (In this example, the list of people and their selections are in a table called 'Main').

So I created a table, Second Choices, where cell A2 is where you enter the name of the film to base the list on (Row 1 is marked as a header row):

From here, the formula in cell B2 is:

=FILTER(Main::A,Main::B=A2,"")

This gives you a list of all the people in Table 1::A who picked that film:

Now you need to get a list of all films picked by those people. So in C2, set the formula to:

=FILTER(Main::B,Main::A=REGEX(TEXTJOIN("|",TRUE,B)),"")

This takes the list of people in column B (those that picked this film) and filters the list of films from Main::B that match any of the names (via a REGEX() match).

Now Column C has all the movies picked by all the people who picked the film listed in A2:

Now comes the easy part. We need a list of the distinct film names, so in cell D2 set the formula to:

=UNIQUE(C,FALSE,FALSE)

And you get:

Now we know the list of unique films, we just need to count how many times each one was selected. For that, set E2 to:

=COUNTIF(C,D)

and you get:

Now we have everything we need - a list of unique films (column D) and a count of how many times they were selected (column E). Now we just need to set F2 to:

=FILTER(D,E=CHOOSEROWS(SORT(E,1,-1,FALSE),2))

This one needs a little more explanation. Working from the inside-out, we take all the counts (column E) and sort them into descending order, we then filter the distinct list of movies (column D) to match the second value from the frequency list):

The reason I do it this way is that there could be tied options for the second-most list (in the above example, there are two movies that both rank '3')

As before, you can hide all the intermediate rows, or you can eschew them altogether by combining the steps into one uber-function:

=FILTER(UNIQUE(FILTER(Main::B,Main::A=REGEX(TEXTJOIN("|",TRUE,FILTER(Main::A,Main::B=A2,""))),""),FALSE,FALSE),COUNTIF(FILTER(Main::B,Main::A=REGEX(TEXTJOIN("|",TRUE,FILTER(Main::A,Main::B=A2,""))),""),UNIQUE(FILTER(Main::B,Main::A=REGEX(TEXTJOIN("|",TRUE,FILTER(Main::A,Main::B=A2,""))),""),FALSE,FALSE))=CHOOSEROWS(SORT(COUNTIF(FILTER(Main::B,Main::A=REGEX(TEXTJOIN("|",TRUE,FILTER(Main::A,Main::B=A2,""))),""),UNIQUE(FILTER(Main::B,Main::A=REGEX(TEXTJOIN("|",TRUE,FILTER(Main::A,Main::B=A2,""))),""),FALSE,FALSE)),1,−1,FALSE),2))

which is why I start off with individual columns for each step lol.

In Numbers 14.4 and later, you can use LET() to simply this a lot:

=LET(people,FILTER(Main::A,Main::B=A2,""),

    all_films,FILTER(Main::B,Main::A=REGEX(TEXTJOIN("|",TRUE,people)),""),

    distinct_films,UNIQUE(all_films,FALSE,FALSE),
    film_frequency,COUNTIF(all_films,distinct_films),
    FILTER(distinct_films,film_frequency=CHOOSEROWS(SORT(film_frequency,1,−1,FALSE),2))

    )

which does exactly the same as my step-by-step table, but it stores each step as a variable within LET() rather than needing its own column of results: