Excel KEEP Duplicates Delete Everything Else - Episode 2513

Microsoft Excel Tutorial: Excel Duplicates Welcome to another episode of MrExcel's net cast. In this video, we're going to tackle a common issue in Excel - removing duplicates without shifting the data. But first, let me tell you about an interesting day I had on September 6. I stumbled upon a CSV file of popular Google searches about Excel, but many of them were too short for a full video. Then, YouTube suggested that I create "shorts" - short videos under a minute long. And that's how this series was born. In this episode, we'll be answering some of the harder questions that I found in that database of Google searches. The first one being, how do we remove duplicates without shifting the data? Many people are selecting just one column and using the "Remove Duplicates" function, which causes the data to shift. But there's a simple solution to this - select all the data, go to Data and Remove Duplicates, and make sure to uncheck the "Continue with current selection" option. This will keep the data in its original place. Now, let's move on to the bonus question - how do we get the duplicates back after realizing we made a mistake? The answer is simple - use the shortcut keys Ctrl+-Z or Alt+Backspace, or click on the Undo button on the Home tab. But what if we want to keep the duplicates and remove everything else? That's where things get interesting. We'll use the Conditional Formatting feature to highlight the duplicate values, then filter by color and delete the remaining data. It's a quick and easy solution. Next up, we have a question about a formula to remove duplicates in Excel. The UNIQUE function is a great tool for this, as it gives us a unique list of values while keeping them in the same order as the original data. But what if we want to remove duplicates based on two columns? We'll use the UNIQUE function in combination with the FILTER function, thanks to a great trick discovered by Leila on her channel. This will give us every unique combination of values from those two columns. And finally, we'll cover the topic of ranking while ignoring duplicates. This is a bit more complex, but we'll use the same trick with the FILTER function to get a list of unique combinations of values. And if you want to see more videos like this, make sure to check out the "shorts" section on YouTube, where you can find quick and helpful Excel tips from me and other creators. Thank you for watching this episode of MrExcel's net cast. If you enjoyed it, please don't forget to Like, Subscribe, and Ring the Bell for notifications. And feel free to leave any questions or comments down below. I'll see you in the next video! Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/ You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/ Table of Contents: (00:00) Keep duplicates and delete everything else (01:07) How to remove duplicates without shifting in Excel (01:17) How to get duplicates back after accidentally removing them (01:27) How to remove duplicates in Excel using a formula (01:50) Bonus question: How to keep duplicates and remove everything else (03:02) A solution for keeping duplicates and removing everything else (04:07) Using conditional formatting to highlight duplicate values (04:18) Filtering to select unique values (04:52) Using the UNIQUE function to get a unique list of values (05:12) Removing duplicates based on two columns using the UNIQUE and FILTER functions (06:04) Clicking Like really helps the algorithm #excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting This video answers these common search terms: Conditional formatting to highlight duplicate values in Excel Excel keep duplicates and delete everything else Excel remove duplicates across columns Excel remove duplicates with data intact Filtering duplicate values in Excel Formula to remove duplicates in Excel How to remove duplicates in Excel without shifting How to retrieve duplicates in Excel Keeping duplicates and removing everything else in Excel Removing duplicate rows in Excel Undoing changes in Excel (Ctrl+Z, Alt+Backspace) Using the UNIQUE function in Excel to remove duplicates Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1216119/ 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