how to create a list of unique values from a column of data into a new column in numbers

Understanding jaxjason's response is almost 3 years old . . .

When I attempt to setup formulas as stated, I get an error on the COUNTIF in Column B, "This formula can’t reference its own cell, or depend on another formula that references this cell." Maybe I'm not understanding how to setup the COUNTIF formula. Could you (or anyone else) clarify? This looks like it will address the need I have if I can get it to work. Thanks!

This worked just fine. Used it to summarise how many issues per release we have. This data was taken from an export from Jira. To anyone interested: It all worked fine in latest Numbers, just make sure you implement all the steps before you see the results. Remember that in the table "Data no dups" you should implement A2 as grabbing the first name, and A3 to use the formula and B2 to use the formulas above. Now this looks slightly confusing, however the solution entirely relies on all parts being implemented. You cannot remove the Count column, or the solution would break.

Remember as well, in the new version of Numbers, comma " , " is replaced by semi-colon " ; ".

do you need a dynamic list that auto-updates as you add information to the other column? or do you mind making a new table with a formula and a little manual work?

Thanks

Jason

I will show the simplest method i have come up with.

I made two tables, one with duplicate names (dups) and one i want the unique list in.

This second table has two columns, one for the names (that i want to see once) and one that counts how many times that name appears in the duplicates list. (the second column can be hidden later if you want). The first cell just points to the first name on the dups list.

Table1 is just a single column with a header row, lists names with some duplicated ("Data with Dups")

Table2 has two columns for this to work. One header row. (Column B can be hidden at a later date)

Column A is going to list the names, each once.

A2 =Data with Dups :: A2

A3 =OFFSET( Data with Dups::$A$1, SUM($B$1:B2)+1,0,1,1)

Column B just has a countif of the value in column A

B2 =COUNTIF(Data with Dups::A:A, A2)

Fill B2 down for all rows

Fill A3 down for all Rows

Remove any excess rows where an error shows up. Or surround the A3 formula with a IFERROR function.

Hope this helps some people sometime.

Jason

Pretty good, but does not work unless the repeating values are all in a bunch. Should probably be used after sorting by group row.