Four answers today:
Excel remove duplicates without shifting
Keep Duplicates Remove Everything Else
Which Row does Excel Keep during Remove Duplicates
Excel Get Duplicates Back
Formula to Remove Duplicates in Excel using UNIQUE and SORT functions
FILTER to keep only certain columns
Ctrl+z or Alt+Backspace to Undo
Topics discussed in this video include:
excel remove duplicates without shifting
excel only include duplicates
excel keep duplicates remove unique
excel keep duplicates remove everything else
excel keep duplicates
excel duplicates keep
how to get duplicates back in excel
can you add duplicates back in excel
excel list duplicates between two columns
excel remove duplicates using formula
excel duplicates remove formula
excel duplicates formula
excel is duplicate formula
in excel duplicate formula
excel no duplicates formula
excel duplicate check formula
excel duplicate values formula
excel rank without duplicates
excel remove duplicates which does it keep
excel ranking with duplicates
excel bring duplicates to top
Table of Contents
(0:00) Excel Shorts
(0:51) Excel duplicates 5 in 55
(1:20) Excel remove duplicates without shifting
(2:08) Excel get duplicates back
(2:55) excel remove duplicates which does it keep
(3:18) Excel keep duplicates remove everything else
(4:55) excel duplicates remove formula
(5:20) remove duplicates two columns formula
Excel remove duplicates without shifting
Keep Duplicates Remove Everything Else
Which Row does Excel Keep during Remove Duplicates
Excel Get Duplicates Back
Formula to Remove Duplicates in Excel using UNIQUE and SORT functions
FILTER to keep only certain columns
Ctrl+z or Alt+Backspace to Undo
Topics discussed in this video include:
excel remove duplicates without shifting
excel only include duplicates
excel keep duplicates remove unique
excel keep duplicates remove everything else
excel keep duplicates
excel duplicates keep
how to get duplicates back in excel
can you add duplicates back in excel
excel list duplicates between two columns
excel remove duplicates using formula
excel duplicates remove formula
excel duplicates formula
excel is duplicate formula
in excel duplicate formula
excel no duplicates formula
excel duplicate check formula
excel duplicate values formula
excel rank without duplicates
excel remove duplicates which does it keep
excel ranking with duplicates
excel bring duplicates to top
Table of Contents
(0:00) Excel Shorts
(0:51) Excel duplicates 5 in 55
(1:20) Excel remove duplicates without shifting
(2:08) Excel get duplicates back
(2:55) excel remove duplicates which does it keep
(3:18) Excel keep duplicates remove everything else
(4:55) excel duplicates remove formula
(5:20) remove duplicates two columns formula
Transcript of the video:
Excel - keep duplicates and delete everything else.
I should tell you, September 6 of this week was a really interesting day.
Two things happened.
In the morning, I found this great CSV file of popular questions that people ask Google about Excel.
But, as I looked through, many were just too short for a full video.
Then, that afternoon, Tuesday, September 6, at 1:26 PM, YouTube nags me.
"Hey, you should be creating shorts".
I'm like, "What the heck are shorts?" These short little videos that are less than a minute.
Billions of views a day.
It struck me - this is a great way - I can take those quick questions and create a short, less than 60 seconds.
You don't need all the production quality. So, September 6 is, "Is Excel infinite?".
September 7, "Excel, sum is zero". September 8, "Mark or remove duplicates".
And in this one, I actually did five answers in 55 seconds.
But then, as I went through that database of questions that people ask Google, I realized there were harder things, things that you couldn't do in less than 60 seconds.
So today, we're going to go a little bit slower and answer these questions.
Excel: remove duplicates without shifting. Keep duplicates, remove everything else.
Oof. Excel, get the duplicates back.
And a formula to remove duplicates in Excel. All right, let's talk about that first one.
People are asking how do we remove duplicates without shifting?
It has to be, what they're doing is they're selecting just this column, like that.
And then, Data and Remove Duplicates.
Now, let's just look at this line down here in red.
Number 18 is “Can Excel remove duplicates?” When I choose "Remove Duplicates" with just that column selected, and that means that I'm ignoring this nagging message and say, "Continue with the current selection”, and then remove duplicates.
Click "Okay".
Ah, 10 duplicate values found and removed. 29 unique values remain, but look what happened.
That data, that line in red that used to belong to number 18, has now moved up.
All right, so let's do the bonus question, "How do we get duplicates back immediately after you realize you screwed something up?" Either Ctrl+-Z, or Alt+Backspace: those are both shortcut keys to undo.
Or, on the Home tab, click undo, and you get everything back.
And so, the proper way to do this to keep that 18 with what's next to it, is you choose all of the data, or actually just one cell in the data.
Data, Remove Duplicates.
See, now it selects the whole range, and you'll notice, can I find duplicates in Excel in is here three times.
Ranked five, six, and seven.
If I would leave both of these checked, it wouldn't remove them because they're not unique across both columns.
But I have to tell Excel, "Don't pay attention to rank. I'm just interested in what's in phrase”.
Another question that came up is which one are they going to keep?
Well, we're about to find out which one they're going to keep, because there's five, six, and seven.
We're going to be ending up with just five.
And the 18, “Can Excel removed duplicates” stayed together.
All right, so, it's interesting that people are asking that question about how to remove duplicates without shifting.
The only thing I can figure is they're selecting just one column instead of all of the data.
This one, editing the Short #3, I got it down to 56 seconds, and then I saw this question.
I realized there's no way that I'd ever be able to do this one.
Someone doesn't want to remove the duplicates.
They want to KEEP the duplicates, and remove everything else. Oh, that's an interesting one.
But I have a great solution, and it's not that hard.
From the top, control-shift-down arrow. Control-backspace.
So now we've selected all those cells.
On the Home tab, Conditional Formatting, Highlight Cells, Duplicate Values.
And you'll see here in the preview that they're marking those three because they're duplicate.
But, did you notice, that here, this is a drop down.
And I can choose "Unique" which will select the things that are not currently red.
Right there. Okay?
So now, it's "Can I find duplicates in Excel?" Is not marked and all the things that appeared exactly once are marked. All right, so far, so good?
Go to one of the pink cells.
Right click, Filter, Filter To Selected Cell's Color.
And now, we end up with all the things that appear in the database exactly one time. Select this data, control-shift-down arrow.
We've now selected all the things that we want to delete.
On the Home tab, Delete, Delete Sheet Rows.
Then, remove the filter, and we're left with just the duplicate values.
How cool is that?
Next question; Formula to Remove Duplicates in Excel. Oh boy, all right.
The unique function will let us get a unique list of customers like that.
It keeps them in the exact same order as the original data.
Just like "Remove Duplicates" would do, but for me, I think I would always want to SORT that.
Okay, but, what if we want to remove duplicates based on two columns?
In essence, what we would get if we did only product and customer - those combinations.
All right, here's what I'm going to do, I'm going to do equal UNIQUE.
I'm going to start with unique, but then the FILTER.
I'll go from product, control-shift-down, and then just shift right, right, right to select that range. What do I want?
I want to get just column one, and column four.
In left curly bracket, I want to put a one for column one.
Zero for column two, a zero for column three, and a one for column four.
Shout out to Leila for that awesome trick that she discovered on her channel, and we get every unique combination of product and customer like that.
All right, there we go, a little bit longer video covering four more filtering topics.
This last one, "ranking, ignoring duplicates," that's in here.
I know that I already have that video.
I'll put an "I" in the top right hand corner, once I can track it down.
So hey, check out these Shorts the next time you're on YouTube. Over on the left hand side, click on "shorts".
You can search for all the Excel shorts. You'll get some from Leila, some from me.
Some other things. It's interesting.
All right, hey I want to thank you for stopping by.
We'll see you next time for another net cast from MrExcel.
If you like these videos, please down below, Like, Subscribe, and Ring the Bell.
Feel free to post any questions or comments down in the comments below.
I should tell you, September 6 of this week was a really interesting day.
Two things happened.
In the morning, I found this great CSV file of popular questions that people ask Google about Excel.
But, as I looked through, many were just too short for a full video.
Then, that afternoon, Tuesday, September 6, at 1:26 PM, YouTube nags me.
"Hey, you should be creating shorts".
I'm like, "What the heck are shorts?" These short little videos that are less than a minute.
Billions of views a day.
It struck me - this is a great way - I can take those quick questions and create a short, less than 60 seconds.
You don't need all the production quality. So, September 6 is, "Is Excel infinite?".
September 7, "Excel, sum is zero". September 8, "Mark or remove duplicates".
And in this one, I actually did five answers in 55 seconds.
But then, as I went through that database of questions that people ask Google, I realized there were harder things, things that you couldn't do in less than 60 seconds.
So today, we're going to go a little bit slower and answer these questions.
Excel: remove duplicates without shifting. Keep duplicates, remove everything else.
Oof. Excel, get the duplicates back.
And a formula to remove duplicates in Excel. All right, let's talk about that first one.
People are asking how do we remove duplicates without shifting?
It has to be, what they're doing is they're selecting just this column, like that.
And then, Data and Remove Duplicates.
Now, let's just look at this line down here in red.
Number 18 is “Can Excel remove duplicates?” When I choose "Remove Duplicates" with just that column selected, and that means that I'm ignoring this nagging message and say, "Continue with the current selection”, and then remove duplicates.
Click "Okay".
Ah, 10 duplicate values found and removed. 29 unique values remain, but look what happened.
That data, that line in red that used to belong to number 18, has now moved up.
All right, so let's do the bonus question, "How do we get duplicates back immediately after you realize you screwed something up?" Either Ctrl+-Z, or Alt+Backspace: those are both shortcut keys to undo.
Or, on the Home tab, click undo, and you get everything back.
And so, the proper way to do this to keep that 18 with what's next to it, is you choose all of the data, or actually just one cell in the data.
Data, Remove Duplicates.
See, now it selects the whole range, and you'll notice, can I find duplicates in Excel in is here three times.
Ranked five, six, and seven.
If I would leave both of these checked, it wouldn't remove them because they're not unique across both columns.
But I have to tell Excel, "Don't pay attention to rank. I'm just interested in what's in phrase”.
Another question that came up is which one are they going to keep?
Well, we're about to find out which one they're going to keep, because there's five, six, and seven.
We're going to be ending up with just five.
And the 18, “Can Excel removed duplicates” stayed together.
All right, so, it's interesting that people are asking that question about how to remove duplicates without shifting.
The only thing I can figure is they're selecting just one column instead of all of the data.
This one, editing the Short #3, I got it down to 56 seconds, and then I saw this question.
I realized there's no way that I'd ever be able to do this one.
Someone doesn't want to remove the duplicates.
They want to KEEP the duplicates, and remove everything else. Oh, that's an interesting one.
But I have a great solution, and it's not that hard.
From the top, control-shift-down arrow. Control-backspace.
So now we've selected all those cells.
On the Home tab, Conditional Formatting, Highlight Cells, Duplicate Values.
And you'll see here in the preview that they're marking those three because they're duplicate.
But, did you notice, that here, this is a drop down.
And I can choose "Unique" which will select the things that are not currently red.
Right there. Okay?
So now, it's "Can I find duplicates in Excel?" Is not marked and all the things that appeared exactly once are marked. All right, so far, so good?
Go to one of the pink cells.
Right click, Filter, Filter To Selected Cell's Color.
And now, we end up with all the things that appear in the database exactly one time. Select this data, control-shift-down arrow.
We've now selected all the things that we want to delete.
On the Home tab, Delete, Delete Sheet Rows.
Then, remove the filter, and we're left with just the duplicate values.
How cool is that?
Next question; Formula to Remove Duplicates in Excel. Oh boy, all right.
The unique function will let us get a unique list of customers like that.
It keeps them in the exact same order as the original data.
Just like "Remove Duplicates" would do, but for me, I think I would always want to SORT that.
Okay, but, what if we want to remove duplicates based on two columns?
In essence, what we would get if we did only product and customer - those combinations.
All right, here's what I'm going to do, I'm going to do equal UNIQUE.
I'm going to start with unique, but then the FILTER.
I'll go from product, control-shift-down, and then just shift right, right, right to select that range. What do I want?
I want to get just column one, and column four.
In left curly bracket, I want to put a one for column one.
Zero for column two, a zero for column three, and a one for column four.
Shout out to Leila for that awesome trick that she discovered on her channel, and we get every unique combination of product and customer like that.
All right, there we go, a little bit longer video covering four more filtering topics.
This last one, "ranking, ignoring duplicates," that's in here.
I know that I already have that video.
I'll put an "I" in the top right hand corner, once I can track it down.
So hey, check out these Shorts the next time you're on YouTube. Over on the left hand side, click on "shorts".
You can search for all the Excel shorts. You'll get some from Leila, some from me.
Some other things. It's interesting.
All right, hey I want to thank you for stopping by.
We'll see you next time for another net cast from MrExcel.
If you like these videos, please down below, Like, Subscribe, and Ring the Bell.
Feel free to post any questions or comments down in the comments below.
