We have a bunch of names in a column. We need to create a unique and alphabetically sorted list. BE CAREFUL! Before we jump into a solution we should ask a couple of key questions. Does the solution need to be dynamic? Do we need to teach the solution to our co-worker or client?

Original Name List

This is our list of names. We need to remove the duplicate names and sort it.

Default Mindset

Those of us who work with data for a living tend to have default preferences based on our experience. Solve using VBA! Solve using array formula! Solve using Pivot Table!

We can easily fall into a trap of quickly starting a solution without asking enough questions.

Answering questions like these will help us create the most appropriate solution (many others questions exist like “How many rows of data?” “How big is the file?”).

If we don’t ask these questions then we risk creating a solution that is overly complicated, not understood and not used. Sadly, we could waste their time and our time.

Simple Questions

“Is this a one time task?” If so, we simply use Excel’s built in functionality (‘Remove Duplicates’ and ‘Sort’) and we are finished in just a few seconds. If the task is repeated on a monthly basis some people will still prefer a simple low tech solution like this. I know, for formula lovers like me it can be shocking but we have to understand and work with our audience.

“There are various ways to solve this. Are you ok using a Pivot Table that requires a simple refresh if your data changes? Fully dynamic solutions are possible but are more complex and time consuming to create” Educate the person that you are helping by explaining the various solutions that exist.

“I can solve this using an Array formula. It’s only one formula but it has many functions and it’s complex to explain. Do you want to understand the solution or just know how to work with it? Sometimes we don’t have to teach people exactly how the formula works but rather teach them how to use it and possibly modify it. In my experience it’s also extremely important to warm them of the pitfalls. For example, does the solution use a dynamic counter using the row function or column function? Can they safely insert rows and columns?

Super Interesting Puzzle!?!

What if you stumble across a super interesting puzzle but the person wants a simple solution? Create the simple not so dynamic solution for them, jot down the puzzle and later on use some sample data to create your VBA or mega array formula solution!

Video & Blog Post Links

I solved this recently using the helper column approach. My YouTube video.

Here are other ways to solve this:

• David Hager generating-a-sorted-unique-array-in-excel-using-only-formulas
• Oscar Cronquist drop-down-list-unique-distinct-alphabetically-sorted-values
• Mike Girvin YouTube video

My Excel File

Here is my Excel file that I used in the video.

…and finally a doodle 🙂