Here's a cool little Excel tip for #TipTuesday. Have you ever had a column of information in Excel and wanted to get a distinct / unique list of values from one of your columns of data? I learned a new thing last week doing just this, and, surprise, it wasn't "create a pivot table with that column"!

Sample Data

Here's my sample data: from the RM00101 table in Dynamics GP's TWO database. I copied the customer ID, name and class into Excel as a quick set of sample data.

My sample data is a list of customers with ID, Name and Customer Class (from Dynamics GP)

Customer Class - unique values

Let's say I want to know what Customer Classes are used in my customer list. Here's how to do it.

  1. Highlight the cells or column of data you want to copy from.
  2. Click on the Data tab, then the Advanced option.
My example is selecting the CUSTCLAS field to copy from

In this pop-up window, you will mark the 2nd Action "Copy to another location", as well as mark "unique records only". This means you only want 1 of each value.

Last: identify where to copy this data. The only limitation is you have to select a cell on the current worksheet, not on another worksheet.

The Advanced Filter menu shows my example of pasting to another locatin with “Unique records only” enabled.

End Result

Here's the end result: starting in H1 both the title and the unique values were pasted along with the formatting (surprisingly enough!). The Title was copied because I highlighted the entire column, and thus it was one of the "unique values".

The result of the paste is in column H showing one of each Customer Class IDs from column C.

This was a neat shortcut and one I'll use many times for various reasons. The previous method I would use to was create a quick pivot table and copy/paste from that to where I wanted the data before I realized there was a way to do it in fewer steps.