Identify or extract unique entries in a list

- [Instructor] Excel has a number of different techniques for identifying duplicate and unique data in worksheets. Sometimes duplication in a large list is a problem. We've got duplicate records in rows two and three here, also rows four and five. Now they pop out immediately, primarily because they're at the top of the list, but typically when you do sort a list, duplicates are likely to become adjacent to one another. Another approach could be, let's highlight where the duplication occurs, and then see how we're going to approach this. I'll click column D and on the home tab, let's go to conditional formatting, highlight cell rules, duplicate values, and already they're being highlighted, and what might catch our eye there is that in rows eight and ten, we've got duplicate social security numbers, looks like it's the same name, but the person, maybe it isn't the same person, we've got different other information off to the right.

It looks like the phone numbers are the same too, but some of the other data is different, so that's an oddity. If we apply this feature by clicking OK, and we scroll up and down, we're likely to see these in pairs, as they are here, so this is certainly one approach to identifying them. The bigger issue for some people is, I don't care where they are let's just get rid of them, and so that's why we have a command available in the data tab, remove the duplicates, meaning remove the duplicate rows. Now, it wouldn't get rid of these rows here because they're not duplicated all the way across.

But here's another thought, all you really want is a new clean list. Maybe you want to hold onto this for a while, but if you want to create a new list, we use an entirely different feature called an advanced filter. We've got room to the right of this on this worksheet, plenty of space out there. Let's click in the data, and on the data tab, you'll see advanced, options for filtering using complex criteria. Well, that's what it's often used for, but not in this case. The advanced filter typically requires what we call a criteria range, but not in our example.

What we want to do is to copy these results to another location. We don't need a criteria range. We give this a quick look, Excel has figured out the extent of the entire list of data there from A1 over and down to K765. We want to copy this result to a new location. I'm going to click cell M1, that's going to be the upper left-hand corner of the receiving area, and we click the box unique records only, so let's get a new list starting in column M of just the records from this list that are unique.

One Terry Anderson who's full-time in the ADC Department, one Victor Bailey in the ADC Department and so on. Click OK, we've got a new list. I'll click one of the cells there, double click the bottom edge, we go down to the bottom. You can see that the list is shorter. So on one sense, maybe that's all we wanted to do, it depends upon your perspective and what it is you're trying to do with the data. We've got a new clean list, let's work with it, maybe we'll just throw this one away. On the other hand, sometimes we want to know where those duplicates have been.

Another approach to this could have been, by the way, and I don't think this is as good, we could've highlighted all the data here, back to on the home tab, conditional formatting. First let's clear the rules from the entire sheet, then come back and highlight cell rules, duplicate values, how about that. Now, again, that's a stronger visual then before. That may or may not be helpful. Here too, by the way, if we apply this and scroll up and down, that could be of use. We've got some duplicate dates, that's okay. We've got some duplicate compensation amounts perhaps, it wouldn't occur that often.

But those are other issues that could come up. Now on a different worksheet here, a different kind of situation. In our organization we've got a few salespersons and we're tracking their sales. And we only have so many of them and so anytime we track a new sale here, we just type in the number. Already each salesperson occurs on this list at least twice maybe more. We don't want somebody typing in an ID number that doesn't exist. And maybe the best way could be, data validation, but we're not going to use that. We use another feature, that has to do with unique.

When we go on the home tab to conditional formatting, as we saw earlier, here's that option for duplicate, but where's unique. We don't see it at first, we don't see it mentioned, but there is a drop arrow, right here, right next to duplicate, unique. Now right now there are no unique entries there. Actually something has been flagged, A1, but we can ignore that and leave it there. And we didn't necessarily have to use that color scheme. But imagine I'm typing in some of the new data here, I'm about to type in 213, you can see that's previously existed, I type 214 and tab over here and immediately I'm alerted to the fact that I put in a number here that probably doesn't belong.

That's a new entry, it's a unique entry. Not just that it's new but it's unique. It doesn't exist previously and actually it's not any of the salespersons ID. So, using unique, probably less likely to use that then you would duplicate in most situations but in this example, maybe it makes more sense. So, different techniques here that we could be using primarily with conditional formatting to bring out the idea that we've got duplicates in some columns and not in others. And then that other technique that we used by way of the advanced filter to create a new list out of an existing list that has no duplicates within it.

Different techniques to handle unique and duplicate data.