Parsing your data to create unique lists

- Sometimes it's necessary to create a unique list based on a series of repeating entries. I've got a list here, over 700 names of various employees within a large company. We've got departments listed in column C. I don't have a list of all the different departments here. This list is not sorted by column C, nor does it need to be. There are two major ways to create a unique list, and although not obvious, they're both relatively simple. In this list here, I've got continuous data all the way down to the bottom.

I'll double-click the bottom edge of the cell, simply to point out how far this goes down. We're at 742. I'd like to have a unique list of departments, perhaps on this same worksheet, maybe off to the right there, in Column M, something like that. So, I'm going to select column C, and then on the Data tab in the ribbon choose the Advanced Filter. Now, if you've used this in the past, possibly the range you're seeing here under List range is not accurate. So in this case, it isn't. I'll simply click column C.

There's nothing else in column C, except departments, so we can simply indicate the data that way. We want to copy our results to another location. If you're familiar with Advanced Filter, you'll know something about Criteria range, but in this case, we don't need anything in that panel at all. Copy to, we do need to fill in this. I could either type M1 or scroll to the right and click on a cell out there to the right somewhere, for example, M1. Unique records only, of course that's key. We must check that box.

Click okay. Our data's been copied. Let's go take a look at it. There it is. This is a unique list. It's not necessarily sorted. Maybe we don't even care for the moment. We could easily click in here and on the Data tab press the A/Z button right here. There's our sorted list, alphabetical, a unique list. Be sure to check this out, because possibly you might have some misspellings. Did everybody spell environmental correctly? Looks like they did in this example. And you could imagine situations where someone might have spelled out Project and Contract Services, or another person might have typed in Admin with a period behind it.

Someone else might have put a period behind Mfg and so on. So do take a quick look at it, just to make sure that the entries are unique or truly unique, as they should be. So you might have to make some corrections there, but for the most part, the feature works directly. Another way to do this, and an unlikely way, but nevertheless fast, we could create a Pivot Table solely for the purpose of getting a unique list of departments. Let's click within the data and go to the Insert tab within the ribbon, and simply choose, for example, Recommended Pivot Tables, if you're familiar with that feature.

If not, maybe simply Pivot Table. If you go with the defaults, we are automatically on the way to creating a Pivot Table on a separate sheet. We'll simply click okay. We're now on a separate sheet. Let's simply click the box for department, and there's our unique list. Now maybe we don't really need a Pivot Table here, but here's our unique list of entries right here, and we'll simply copy/paste that or do with it as we wish, but we've created a unique list simply by creating a Pivot Table on the field in question.

Here it is automatically sorted as well. So two quick ways to create unique lists, one by way of Advanced Filter, one by way of a Pivot Table.