Excel4Business - Our Blog

Excel Tips and Tricks from our Experts

Expert Excel Help – Sorting Unique Entries

Microsoft Excel can be used for simple but effective data organization. Today's tip shows how to take a selection from a large data set in which elements are repeated and filtered so that all repetitions are removed, leaving only a set of unique entries.

Firstly, we have a typical list of repeat customers alongside their various invoice numbers. As you can see, returning customers are included. We would like to sort through this to create a list of unique customers. Step one is to copy the heading from the the target column, Customer.

We then simply place the cursor anywhere in the target field and select Data from the menu bar. From there, click on Advanced in the Sort & Filter panel. Be aware that Excel may try to dictate the coordinates of the List range field; If so, simply overwrite, in this case with $A$2:$A$16.

As can can be seen in the example, we have selected the Copy to another location radio dial. That other location needs to begin from the first available cell under the new Customer heading. We use the formula, Sheet1!$D$2 to allow the list of names to fill down. Finally, and importantly, check the Unique records only option, then click on OK.

We have now extracted a list of unique customers. For more details on how to use the Sort & Filter and Advanced filter, check the Microsoft website here, or contact one of our experts.