How to find exact duplicate lists in an app

This is actually pretty easy to do with a couple of helper tables and/or columns.

The first thing to do is create a table of unique films. To do this, create a new table with one column and enough rows to catch all the unique film titles (note, that technically, this step is optional, but it helps to explain the process)

In cell A1 in this table, set the formula to:

=SORT(UNIQUE(Table 1::B,FALSE,FALSE))

Now this table will have a list of the unique films which will dynamically update to show the nominated films.

Now, back in your main table, add a new column, C, with the formula in C2 set to:

=MATCH(B,Films::A,0)

and fill down the column. This will assign an index number to each film, based on its position in the list.

Now what you need to do is use those index numbers to create a key that represents this person's choices. To do this, add a new column, D, and set the formula in D2 to:

=ARRAYTOTEXT(FILTER(C,A=$A2,0),1)

and Fill Down.

What this will do is first FILTER() the list of index values to just catch the ones where the value in column A matches $A2 (i.e. the indexes of all of John's films).

You should get something like this:

Note that I've used a subset of your data since I couldn't be bothered to typo out all the films :)

But the concept should work for any number of people, and any number of film choices.

If any two people have identical lists, their key will be the same. If there are any differences, including the same movies in a different order, then their key will be different.

Now you have a unique representation of each persons film choices. You can use this column to find the duplicate values which represent equivalent lists of movies.

There are a couple of ways you can go from here...

The next thing I would do is create an another table that lists the unique names and their choices. This can be done with the following formulas:

A2:

=SORT(UNIQUE(Table 1::A,FALSE,FALSE))

This will return a sorted list of unique names from the main table.

B2:

=LOOKUP(A,Table 1::A,Table 1::D)

Now you should have something like:

As a final step, you can filter the list of index keys to find the duplicates, using something like:

=FILTER(Choices::A,COUNTIF(Choices::B,Choices::B)>1,"")

This will return a list of names from Choices::A where there are more than 1 similar values in Choices::B

This is what my sample worksheet looks like at the end of all this:

and the general flow is something like:

Once you're set, you can hide the helper columns and/or tables.

Glad I could help.

In retrospect, I thought of one way to simplify the process a little - my original model generated an index number for each movie, then built an textual representation of those indexes, like:

Mark: {9;1;11}

You could eschew the index numbers and just build a text value that concatenates all a given person's choices. In other words, instead of Column D using ARRAYTOTEXT(FILTER(C,A=$A2,0),STRICT) you could just:

=CONCAT(FILTER(B,A=$A2,""))

which creates something like:

John:Raging BullA Clockwork Orange The Godfather

and then use the Choices table to this as a unique string to find common choices. It eliminates one level of SORT(UNIQUE()) on the film names, although it might be nice to have that as a reference table - you could easily extend the Films table to show a count of each title, so you know which ones get nominated the most.

Choose Utilities from the Finder's Go menu, open the Script Editor, and run:

set theFilms to {}

set duplicateList to "Duplicates: "

set filmList to "(paste the films here)"

set authorList to "(paste the authors here)"

repeat with thisFilm from 1 to (count paragraphs of filmList) - 9 by 10

set filmsVoted to paragraphs thisFilm thru (thisFilm + 9) of filmList as string

if filmsVoted is not in theFilms then

set theFilms to theFilms & filmsVoted

else

set duplicateList to duplicateList & paragraph (round (thisFilm / 10) rounding up) of authorList & " "

end if

end repeat

items 1 thru -2 of duplicateList as string

This will only return the second in any pair of duplicates.

(264257)