Excel tip: Copy & Paste Unique Values

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.

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.

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.

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”.

This was a neat shortcut and one I’ll use many times for various reasons. The previous method I would use 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.

comments powered by Disqus
Built with Hugo
Theme Stack designed by Jimmy