Create column with unique values from another column

Heather K

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?

Paul Newcome

Try adjusting your checkbox column formula to only check the box if that row is the 2nd or more occurrence.

 

=IF(COUNTIFS([Requestor Cell Link]$1:[Requestor Cell Link]@row, [Requestor Cell Link]@row) > 1, 1)

 

Once you drag fill down it will count from the top of the sheet to the current row how many times that particular name is found. If it is found more than one time, then it will start checking the box along with any other future occurrences further down the sheet.

Paul Newcome

Try adjusting your checkbox column formula to only check the box if that row is the 2nd or more occurrence.

 

=IF(COUNTIFS([Requestor Cell Link]$1:[Requestor Cell Link]@row, [Requestor Cell Link]@row) > 1, 1)

 

Once you drag fill down it will count from the top of the sheet to the current row how many times that particular name is found. If it is found more than one time, then it will start checking the box along with any other future occurrences further down the sheet.

Heather K

Perfect. Thank you so much!

Paul Newcome

Happy to help. 

Ben Foran

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!

Paul Newcome

@Ben Foran Happy to help! 👍️

Jim B

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

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

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,

Jim