Hi, this is Wayne again with a topic “Dealing with Duplicates in Excel and Finding Unique Data”.
In this Excel tutorial, we’re going to tackle the problem of duplicates so here in this spreadsheet, I have an inventory for a hypothetical music CD store, let’s say, and it specializes in the best music ever created, which is synth pop and 80s new wave. I’M sure you knew that already, but you can see here a list of some really wonderful bands and there’s other data here as well – and this is great, but somehow some duplicates have cropped into this spreadsheet. So you can see here, Alphaville a German band, that’s famous for their song forever, young and also big in Japan. They’Ve recently come out with another album called strange attractor and you can see here for some reason. I have two records of that album and browsing down the spreadsheet a little bit.
I can see that there’s a duplicate here from the most recent OMD CD and even the killer’s. Wonderful, wonderful, so there’s some duplicates going on here and maybe that’s not a problem. Maybe it is just depending on what the purpose of the spreadsheet is, but in this case I don’t want the duplicates, I want to get rid of them, I’m just going to click and drag to highlight the range of data, so everything that I have put in Here, that’s all selected and then I’m gon na go here to conditional, formatting and I’ll select, highlight cells, rules, duplicate values. Now I’m gon na leave it just as the default here, but if I wanted to, I could switch from duplicate to unique. That would highlight just the unique data, but I’m gon na stick with duplicate and I’ll leave it as it says here, light red fill with dark red text. But if I wanted to, I could change that to some other format. I’M gon na click. Ok – and this identifies for me, duplicates now it’s okay, if the word synth-pop is duplicated right, there’s lots of great synth-pop music represented here.
So it’s okay, that the word synth-pop is repeated. I don’t worry about that, but if I see these first two columns highlighted in red that tells me that there’s a duplicate somewhere, okay, so I have five or six, maybe seven diff in albums that are duplicates so now at this point, because it’s only six or Seven I could solve that just by selecting the row just by clicking on that row. Number in fact I’m gon na right-click on it and choose delete, so it deletes that duplicate that was binary Park.
You can see now because I deleted the duplicate binary park. Does not show up in red, so this is a great solution for duplicates. It highlights those things that might be a problem, they might be duplicate records and then you just delete the ones that you want to delete, but imagine, instead of a spreadsheet, with thirty to thirty one records. What if it had five thousand ten thousand. I wouldn’t want to delete those duplicates one at a time, so instead I can go here to data and on the data ribbon, you’ll, see a data tools group and there’s an option here to remove, duplicates but notice that it’s grayed out.
I can’t use it. The reason why is because I have a whole range selected, I’m just gon na click on one particular cell in this range. It doesn’t matter really where it is and now look remove. Duplicates is an option and I’ll click on that to delete duplicate values, select one or more columns that contain duplicates. So what is the duplicate that I care about? How about band and album so the rest of these I’m going to uncheck and then I’ll click? Ok, six duplicate values found and removed, 24 unique values remain, and so you can see because I chose band and album even though Alphaville the band is duplicated and Depeche Mode is duplicated and the killers are duplicated because the album names were not always duplicated, it didn’t Get rid of records that I would like to keep so I have three different alpha VLEs listed two different erasers, two different Depeche, Mode’s, etc.
Let’S try it again, but this time when I do remove, duplicates I’m just gon na say band, so I don’t want any duplicate bands at all. I click okay. Now there are no duplicates, but I got rid of some albums by some of these bands that I wanted to keep so hopefully that makes sense as to why I selected both band and album so looking for duplicates in both all right, I’m gon na undo that Even though it’s exactly what I wanted and for this last example, let’s assume that somebody calls up my new wave synth-pop store and says I don’t care about the albums of the Bands that you have. I just want a list of all the bands whose music I could find in your store. Could you give me that list so, of course I could use that technique.
I just showed you. I could use that and then copy-paste the list send it to the person, but here’s another way. You can do the same thing. I’M just gon na click in column, a let’s say on binary Park.
This is a great new album by them and then on the data tab and ribbon the sort and filter group and I’m gon na click on advanced and it pops up with this window that I can use to do some things and the first thing I’d like To do is I’d like to copy to another location, I’d like to copy a list of the bands to another location – let’s say over here to the right, but it could be anywhere next. Let’S look at the list range that Excel chose. For me, it shows a1 through a 62, so that sounds right. It’S column a next I could put in a criteria range, but in this case I don’t want to do that. I’M just gon na leave that blank and I’ll say copy what I find and I’m gon na click here on this button and then I’ll click where I want it to appear.
I would like it to copy that data to a cell. I so I click there on that cell and that’s where the new data will start and then I can click this button here and click. Ok.
Actually, before I click OK, I need to check this box that says unique records only. I click, OK and look what it did now. It formatted it for me – and I didn’t necessarily want that to happen, but that’s okay.
I could highlight the column, go to conditional formatting and clear the rules from the selected cells. Okay. So that’s more like what I was looking for, but this produced for me Alice of just the unique records in the column that I had clicked. So I had clicked here anywhere in column, a I went to data sort and filter advanced and followed those other steps, and now I’ve got a list of unique bands that I can copy/paste send it to someone do whatever I need to do with it. So those are some tips and techniques to help you with the problem of duplicates in Excel and getting a unique list of items thanks for watching.
I hope you found this tutorial to be helpful. If you did, please click the like button below and consider connecting with me on my social media websites like Facebook, Pinterest and Twitter definitely do subscribe to my youtube channel for more videos about technology, for teachers and students, and when you do, please click the bell. Next to the subscribe button that will let you know whenever I post another video and watch for another video from me at least every Monday. If you’d like to support my youtube channel, please consider becoming a patron of mine through my patreon account and you’ll find links to that in the description below also, if you’re interested in any of this great music. Please look in the description below .