Get a list of unique rows in a column

Question

Level 1

44 points

Get a list of unique rows in a column

In Apple Numbers, I have a sheet called VOCAB LIST. In Column A, it has the following rows:

Dog
Dog
Horse Rider
Cat Club - Furry Pals
Dog
Fox Home & Wolf Den
Frog Lilly Pad: A green home
Dog
Bear
Dog
Ape Mountain: Hidden in beauty & nature

On a separate tab (in the same spreadsheet), I want to create a formula that takes all the rows from "VOCAB LIST" column A, but omits the duplicates.

E.g. so I end up with the following rows on the seperate tab:

Dog
Horse Rider
Cat Club - Furry Pals
Fox Home & Wolf Den
Frog Lilly Pad: A green home
Bear
Ape Mountain: Hidden in beauty & nature

Is it possible to do this? I thought I could use the unique function. And so tried this: =UNIQUE(VOCAB LIST::Table 1::A) but it comes up with an error symbol (although the symbol doesn't state what's wrong.

Any ideas? Thanks!

ENVIRONMENT:

Numbers 13.1

MacOs 12.7.3

MacBook Pro 15″

Posted on May 4, 2024 8:36 AM

Reply

Answer 1

Best reply

SGIII

Level 8

35,348 points

May 4, 2024 4:08 PM in response to big_smile

big_smile wrote:

Is it possible to do this? I thought I could use the unique function.

My version of Numbers doesn't have a UNIQUE function. Does yours?

A filter might be the easiest way to do what you want to do.

After applying the Is Distinct filter, select the visible cells, command-c to copy, click once in the a of an existing table you have set up to receive the values, and command-v to paste.

SG

Reply

Answer 2

Badunit

Level 7

26,158 points

May 4, 2024 6:07 PM in response to big_smile

Excel's UNIQUE function would be handy but Numbers does not have it (at least not yet). If you need a formula for Numbers, here is a set of formulas.

Formulas inthe second table are:

A2 =IFERROR(TEXTBETWEEN(B2,"|","|"),"")

Fill down to complete the column

B2 ="|"&TEXTJOIN("|",TRUE,Table 1::A)&"|"

B3 =SUBSTITUTE(B2,"|"&A2,"")

Fill down from B3

Hide column B

It is up to you to ensure you have enough rows to list all the unique items. If there is at least one blank row at the bottom, you have them all.

Reply

Answer 3

Badunit

Level 7

26,158 points

May 7, 2024 4:48 AM in response to big_smile

big_smile wrote:

Thanks, this is super helpful! I tried to re-create what you posted, but it doesn't seem to work the same for me.

Any ideas? Thanks!

Yes.

Look at the formulas I posted and what cells they go in and look at the screenshot I posted vs the ones you posted. There is an obvious difference in your table vs mine. Nowhere did I do anything with column C. My table does not even have a column C.

Reply

Answer 4

big_smile Author

Level 1

44 points

May 7, 2024 4:57 AM in response to Badunit

Whoops! Somehow I misread B3 as C. It's all working now.

It looks like there's no longer the option on Apple Communities to mark an answer as correct (unless I am missing something). But your answer is indeed correct, so a big thanks for all your help!

Reply