Find Unique Records in Microsoft Excel

Microsoft Excelnot very good with Microsoft Excel. People often think that I am becuae I can concatenate data and write if statements and other formula, but, beyond this, my knowledge is very shallow.

There are things that I know you must be able to do, but have no idea how. As such, I often default to loading data into SQL Server to manipluate. When dealng with large quantities of data, I think this is still the best thing to do, but for smaller datasets, using Excel makes more sense.

There may be a number of other posts, covering fairly simple functionality in Microsoft Excel, that I post in future as reminders for myself.

The first of these Excel tips, is identifying unique data. In Excel this is actually quite simple (once you know how).

If I wanted to identify how many unique vendor ids I had in a dataset, I can do this in only a small number of steps.

Select the columns containing the data I want to search; in this case, the Vendor ID and Vendor Name columns. Select the DataSort & Filter section, click Advanced:

Microsot Excel

As I selected two columns of data with no identifiable column headers, a warning will be displayed; click OK to use the first row:

Identifiable column header warning

Mark the Copy to another location radio button:

Advanced filter: Copy to another location

Click the icon next to Copy to and select the range of cells to use:

Advanced filter: Copy to

Mark the Unique records only and click OK:

Advanced filter: unique records only

In the main Escel sheet, a list of the unique data will be returned:

Microsoft Excel showing unique data

What should we write about next?

If there is a topic which fits the typical ones of this site, which you would like to see me write about, please use the form, below, to submit your idea.

Your Name

Your Email

Suggested Topic

Suggestion Details

3 thoughts on “Find Unique Records in Microsoft Excel

Leave a Reply

Your email address will not be published. Required fields are marked *