Find Unique Records in Microsoft Excel

July 6th, 2018 by

Find Unique Records in Microsoft Excel

Microsoft ExcelI’m not 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

Read original post Find Unique Records in Microsoft Excel at azurecurve|Ramblings of a Dynamics GP Consultant

Source: azurecurve

Leave a Reply