Create column with unique values from another column

Heather K
Heather K ✭✭
edited 12/09/19 in Formulas and Functions

I am working on creating a sheet to be used for reporting. I have pulled in the contact list of names using the cell linking feature however, there are multiple rows with the same person. I would like to create a column listing just unique people. I started by creating a duplicate checkbox column using the formula:

=IFERROR(IF(LEN([Requestor Cell Link]1) = 0, "", IF(COUNTIFS([Requestor Cell Link]:[Requestor Cell Link], [Requestor Cell Link]1) = 1, 0, 1)), 0)


What I am looking for is to have a Requestor column that I would like to pull all of the unique values into. I tried to do so with the following formula:

=IF([Duplicate Check]1, 0, [Requestor Cell Link]1)


I am able to get the names with the checkbox unchecked however, I am missing all of the names that have duplicates. I still need their name, but just once. How can I do that?



Best Answer


  • Perfect. Thank you so much!

  • Ben Foran
    Ben Foran
    edited 01/05/20

    Great work Heather and Paul. There are a few threads using various formulae to show only the unique names in additional columns which can then be filtered out. This method is more elegant and efficient because the original fields (in this case requestor cell link) can be referenced directly with the duplicate check as the filter. Many thanks!

  • Jim B
    Jim B ✭✭✭

    This is great. Does anyone have a row formula that performs the equivalent?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Jim B You would need two helper columns. An auto-number column (called "Auto" in this example) and a text/number column (called "Row" in this example) that contains the following column formula:

    =MATCH(Auto@row, Auto:Auto, 0)

    You would then adjust the COUNTIFS to reference entire columns and include a range/criteria set where the range is the Row column and the criteria is "less than or equal to Row@row".

    =COUNTIFS(..........................., Row:Row, @cell<= Row@row)

  • Jim B
    Jim B ✭✭✭

    Nice one, Paul.

    I found a solution in the end using helper columns with an auto number in one, min(collect in the other and a check to see if they are the same: Clunky though it is, it made me very happy.

    (As you can see my use case involves collating email addresses where I want to capture only unique addresses. And I need the column formula because my sheet auto-updates so I can't be worrying about drag-filling down formulas all the time)

    Anyway, I did come back here to shout about it in case anyone else was looking for an answer but then I realised my solution only works for me because I clear out my sheet once a week and go again with a fresh data set (it being a fresh distribution list for a weekly internal mailer) and is therefore probably not all that useful for the rest of the community.

    Your solution is way neater than mine. And a great one to remember going forwards. I was trying something similar with INDEX (not realising it was MATCH I needed).

    Thanks so much for sharing your expertise here. I've learnt so much from your posts as I'm sure many other people have too.

    All the best,


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!