Make and customize your own tab on Office Ribbon.Indexing 'bug' in Microsoft Word tables.A simple check for Plus addressing with your email address.Citation and Bibliography tools beyond Word.Ctrl + Enter shortcut in Outlook for Windows.Outlook for Windows essential shortcuts.Two ways to switch between Letter and A4 paper sizes in Microsoft Word.Check out the new look Microsoft Office on an iPhone.Dictate ‘speech to text’ on OneNote for Mac. How to use Mac screen sharing for computer help.Microsoft blocks Excel XLL add-ins – at last!.Transparency Color problem and fix for Microsoft Office.Make a transparent shape to mask a photo in Word, PowerPoint and more.Then Data | Get Data | From Other Sources | From Table/Range. It’s something we explain and workaround in our book Real Time Excel (because it’s very important for stock price updating).įirst, click in a cell of the source table you want to use. PowerQuery works on its own updating schedule. Unlike other Unique options, this PowerQuery trick does NOT update automatically, not even if you press F9. PowerQuery has other tricks available like automatic sorting, ignoring blanks and even replacing values. It’s especially useful if you’re importing data from outside sources or using PowerQuery to do other things with the same data anyway. This works quite well but isn’t available across all current Excel releases. Then use PowerQuery’s options to make a unique list and drop the result back into the worksheet as another table. PowerQuery can accept an existing Excel table as a data source. Normally PowerQuery is used to get data from outside sources before massaging it ready for an Excel worksheet. Using PowerQueryĪ sneaky way to get a unique list of values is with PowerQuery. Ctrl + Shift + Enter tells Excel to make an array formula. The new Dynamic Arrays are NOT needed but you must end typing the formula by pressing Ctrl + Shift + Enter instead of just Enter. Keep clear the cells below the main cell with the formula. =IFERROR(INDEX($B$2:$B$14, MATCH(0,COUNTIF($E$1:E1, $B$2:$B$14), 0)),"") =IFERROR(INDEX(ShortPeople, MATCH(0,COUNTIF($E$1:E1, ShortPeople), 0)),"")ĪRRAY FORMULA! This is an array formula, meaning the results spill into other cells. Let’s say one of the seven dwarves has gone walkabout, the new automatically made list of values removes ‘Doc’ automatically (left).įar easier to copy and paste one of these formula (one with a table reference) then change the parameters to suit. If the list of stores, regions etc change an automatic list of unique values will update automatically. There are two main reasons why it’s better to produce unique lists that automatically update from the original data. After all, the question goes, “We know the list of departments, regions etc”, just type them in and move on. Why bother?Ī surprising question about making a unique list is why it’s even necessary. There is a ‘unique’ option table filtering option but it’s a ‘one off’ action that doesn’t refresh. We’re talking here about making a list of unique (non-duplicate) values which update automatically if the source list changes. The best option is a lot easier and relatively new in Excel. Making unique (not duplicated) list in Excel is possible in three ways that update automatically.
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |