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”!
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.
- Highlight the cells or column of data you want to copy from.
- 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.
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.