Excel - Marking Subsequent Duplicates in Excel with VBA - Episode 1326
Microsoft Excel Tutorial: Conditional Formatting offers the ability to mark duplicates or uniques, but it sort of missed the boat. Today, in Episode #1326, Bill looks at how to use a formula-based conditions with VBA to fill voids left by Conditional Formatting.
Welcome to the MrExcel podcast. In this episode, we will be discussing Excel VBA and Macros, specifically focusing on chapter 15 and the topic of marking duplicates. I'm Bill Jelen and I'm excited to share some insights and tips with you.
In Excel 2007, a new feature was introduced called conditional formatting rules, which included the option to mark duplicate values. While this was a step in the right direction, I believe they missed the mark. The built-in options only highlight the duplicates or uniques, but not in the way that is truly useful. In this video, I will show you a more efficient way to mark duplicates and uniques using a formula and VBA.
Let's take a look at the example in the video. You'll see that the built-in duplicate option highlights the second set of values, which is not what we want. We want the first set of values to be highlighted as they are the truly unique values. This is where my "wishful thinking" method comes in. By using a formula that is relative to the first active cell, we can accurately highlight the first set of values as desired.
To achieve this in VBA, we simply add a condition as an expression and pass a formula as an argument. The formula must be relative to the first active cell, which in this case is E2. By using a clever combination of dollar signs and no dollar signs, the formula will adjust as we go down the cells. This method may be a bit more complex than using the built-in options, but it is much more accurate and efficient.
I hope this video has been helpful in showing you a better way to mark duplicates and uniques in Excel using VBA. It would be great if Microsoft could add this as a built-in option, perhaps calling it "Bill Jelen Unique" or something similar. But until then, we can use this method to get the desired results. Thank you for watching and be sure to check out our other netcasts for more Excel tips and tricks. See you next time!
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-...
Table of Contents:
(00:00) VBA Marking Duplicates using Conditional Formatting Rules
(00:17) Built-in Duplicate and Unique Values
(00:27) Desired Unique Values
(00:37) Wishful Thinking Macro
(01:04) Formula Version
(01:37) VBA Code
(02:56) Microsoft's Lack of Useful Unique Values
(03:11) 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 #tutorial
This video answers these common search terms:
Bill Jelen's unique value formatting solution for Excel
Conditional formatting in Excel 2007
Creating a list of unique values in Excel
Creating a macro for unique value highlighting in Excel
Excel VBA chapter 15
Highlighting duplicate values in Excel
Highlighting unique values in Excel
Marking duplicates in Excel
Microsoft's built-in duplicate and unique value formatting options in Excel
Using formulas for conditional formatting in Excel
Using VBA for conditional formatting in Excel
Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads...
Смотрите видео Excel - Marking Subsequent Duplicates in Excel with VBA - Episode 1326 онлайн без регистрации, длительностью часов минут секунд в хорошем качестве. Это видео добавил пользователь MrExcel.com 19 Январь 2011, не забудьте поделиться им ссылкой с друзьями и знакомыми, на нашем сайте его посмотрели 1,120 раз и оно понравилось 5 людям.