Automatically Remove Duplicates from Drop Down Lists
📗 Get Your Video Workbook Template Here: »» https://cutt.ly/up4v2007MFD
🎯 Get rid of duplicates in your dropdown list for good! Make your dropdown lists shorter and easier to use….automatically remove any duplicates with these techniques.
👀 Excel 365 Users MASSIVE Shortcut:
You can use a single formula in cell E2 to spill a sorted list of unique Product Sub Categories
=SORT(UNIQUE(Table4[Product Sub-Category]))
Replace Table4 with whatever your main data table ends up being called.
To link a data validation list you can set the list to $E$2[Hash Tag Symbol]. The Hash Tag Symbol means the spilled list from E2 onwards but I can't include it here as it will create an unwanted link.
WARNING: This new method only works dynamically for data validation lists, and not for Form or Active X dropdowns. You will still need VBA or a dynamic named range to link this spilled list to these controls or use the method taught in this video.
======================================================================================
🕑 Video Timings
00:00 The Issue to resolve
01:05 How to Manually Remove Duplicates
02:18 Setup Spreadsheet for Automation
03:56 Record and Edit Main Code
09:23 Link Up Dropdown Lists
11:48 Test Adding Extra List Items
13:12 Fully Automate Duplicate Removal
16:11 Add Professional Touches
======================================================================================
#Up4Excel
#Up4ExcelVBA
#Up4ExcelAdvanced
======================================================================================
📖 say i want to link these drop-down lists here to this column of data i don't want any duplicates how am i going to do it i'm going to show you
right so i've got on screen an absolute shed load of data here so it's about 8 000 rows and i put all that on just to demonstrate that this can system can cope with pretty much anything so what i want to do is i want these drop down lists to link to this column product subcategory but i don't want all these duplicates at the moment if i just show you a data validation list for example data validation if i say that i want my source to be all of that and okay it go back home and click down we've got a ton of duplicates in there and not only that we don't even get it finishes on uh it's reached its maximum so we don't want that at all what we want is a unique list first off you could do it manually so if i just insert a couple of rows because i'm going to be putting my list somewhere around here which i want it roughly the same width as that 212. i could just take that list holding ctrl shift and down arrow
control c back up here paste it and then i could go to data remove duplicates make sure only that's selected says it had a header row okay there we go i now have a unique list and i can just link i'm just using this one as an example but i can now link it to there right job done but not exactly dynamic is it um anything happens to this list in the future you know new products sub categories come in nothing happens here it's not in any way linked so we want to link this up but going to use some vba to effectively kind of recreate that and refresh it at will if i remove that so the first thing i want to do is change this data into a table so ctrl t hit enter that'll give me a table don't really like the look of it pick this one
why did i do that right the reason i did that is because when you add new names anything to the bottom of the table the table so i'm going to do this the table expands just so that's going to be quite a useful property because what it means is if we're linking anything to this table we're automatically taking account of any new data that's coming in so product subcategory is the one i want to use so what i'm going to do is highlight all of that so i'll use the arrow keys and control shift to do that and then over here i'm going to give that a named range i'm going to call this product uh subcat perhaps even put all on the end and then hit the enter key
======================================================================================
Here at Up4Excel we’re on a mission to help YOU:
» Get your Excel skills UP and your task time DOWN
» Focus on shortcuts and fast impressive results
» Improve your productivity and free up your time
Everyone will assume you work 24 hours a day to produce the kind of output you’ll be producing in no time…. with the help of Up4Excel training.
You get new video releases every week, packed full of ways to save time and impress those around you.
💎 Don't miss out and fall behind.....
🅾 SUBSCRIBE NOW 🅾 https://cutt.ly/Up4ExcelSub
======================================================================================
💥 Get a Shortcuts Cheat Sheet: »» http://www.up4excel.co.uk/shortcuts
🔓🔑 Remove Excel File Open Passwords: »» • Excel File Locked? Learn How to Break...
🎁 Your small gift will help me make better videos for you and others.
Thank You: https://www.paypal.me/Up4Excel
Watch video Automatically Remove Duplicates from Drop Down Lists online, duration hours minute second in high quality that is uploaded to the channel Up4Excel 27 April 2021. Share the link to the video on social media so that your subscribers and friends will also watch this video. This video clip has been viewed 12,315 times and liked it 55 visitors.
