The classic problem: unstack a column of records in Excel. This video shows a cool way to get this stuff un-stacked. You’ll see the FILTER Dynamic Array function, the SEQUENCE Dynamic Array Function, and even the SORTBY.
You’ll see cool uses of regular Excel functions that act like arrays. This is real fire, baby!
https://ozdusoleil.com/wp-content/uploads/2019/01/excel-dynamic-arrays-unstack-a-column-of-records-565-youtube-thumbnail.jpg7201280Oz du Soleilhttps://ozdusoleil.com/wp-content/uploads/2017/01/oz-logo-v4-300x73.pngOz du Soleil2018-12-04 21:01:212020-11-03 09:42:14Excel Dynamic Arrays: Unstack a Column of Records
Rahim Ali asked me about how to keep only capitol letters. For example:
France: F United States: USA Republic of China: ROC United Arab Emirates: UAE South Korea: SK
(In this video I use names as examples to work with.)
This can be done with Excel’s Flash Fill but that’s not a dynamic solution. For something dynamic we use Power Query and it’s a little tricky and we make clever use of Add Column from Example.
We have to go into the m-code and convert: Text.Remove to Text.Select. And then we can have Power Query select only A thru Z
And then … a TWIST!
We’ve got a problem with the character: É
I show you one way of dealing with that!
This video also includes a summary of Excel Days 2018 in Sofia, Bulgaria where I had the pleasure to give the keynote presentation and teach a 7-hr Master Class on Excel and Power Query.
https://ozdusoleil.com/wp-content/uploads/2019/01/power-query-keep-all-capital-letters-with-text-select-in-m-code-1675-youtube-thumbnail.jpg7201280Oz du Soleilhttps://ozdusoleil.com/wp-content/uploads/2017/01/oz-logo-v4-300x73.pngOz du Soleil2018-11-27 08:18:432020-11-03 09:42:16Power Query: Keep all Capital Letters with Text.Select in M-Code
https://ozdusoleil.com/wp-content/uploads/2019/01/excel-dynamic-arrays-6-examples-of-filter-with-multiple-criteria-2722-youtube-thumbnail.jpg7201280Oz du Soleilhttps://ozdusoleil.com/wp-content/uploads/2017/01/oz-logo-v4-300x73.pngOz du Soleil2018-11-15 00:33:032020-11-03 09:42:16Excel Dynamic Arrays: 6 Examples of FILTER with Multiple Criteria
Dynamic Array Functions in Excel are so fabulous … BUT!
As with anything, there are traps, warnings and caveats. In this video I quickly cover 5 things you should know about Dynamic Array Functions and Formulas.
https://ozdusoleil.com/wp-content/uploads/2019/03/5-dynamic-array-gotchas-in-excel-6187-youtube-thumbnail.jpg7201280Oz du Soleilhttps://ozdusoleil.com/wp-content/uploads/2017/01/oz-logo-v4-300x73.pngOz du Soleil2018-10-24 21:00:402020-11-03 09:42:195 Dynamic Array Gotchas in Excel
7 new Dynamic Array functions have been added to Excel! In this video you’ll see the FILTER and SORT functions and how 4 formulas bring back results in lots of cells!
Also: – There’s new nomenclature using the # to reference a dynamic range. – The new SPILL error lets you know that your result doesn’t have enough room. So, you need to move something and make room.
https://ozdusoleil.com/wp-content/uploads/2018/10/dynamic-arrays-sort-filter-and-dynamic-vlookup-91-youtube-thumbnail.jpg480640Oz du Soleilhttps://ozdusoleil.com/wp-content/uploads/2017/01/oz-logo-v4-300x73.pngOz du Soleil2018-10-22 21:00:062020-11-03 09:42:20Dynamic Arrays: SORT, FILTER, and Dynamic VLOOKUP
There’s a lot in this video! When you get this working, you’ll be entitled to wear your underwear outside your pants like a true Super Hero!
The challenge: We’d like random names, but also choose which format we’d like our result in: – First Name Last Name – Last Name, First Name – First Name MI Last Name – Last Name, First Name MI
I thought about using a custom function but realized it’d be simpler to drill down (pass a parameter).
In this video you see: – Column by Example – Excel Tables – Power Query – Left Outer Join – M Code – Merge Columns – and EVVYTHANG!
https://ozdusoleil.com/wp-content/uploads/2018/10/pass-parameter-drill-down-to-pick-name-format-with-power-query-youtube-thumbnail.jpg7201280Oz du Soleilhttps://ozdusoleil.com/wp-content/uploads/2017/01/oz-logo-v4-300x73.pngOz du Soleil2018-10-09 08:53:512020-11-03 09:42:23Pass Parameter (Drill Down) to Pick Name Format with Power Query
Ken Puls of ExcelGuru presented a 3rd Excel Challenge and here’s my solution.
The challenge is to take a word, assign values to each letter and generate a total for each word.
A = 1 a = 1 . . Z = 26 z = 26
The word ‘Attitude’ gets 100 points.
As I started thinking about a solution I started thinking too hard about it. I wanted something more crafty or sophisticated, but then I realized that I saw a path toward success and that’s what mattered.
So, this video is really about understanding your strengths and style and not apologizing. My Excel style is akin to the style of Jerome Bettis, the former runningback for the Pittsburgh Steelers. He was big and powerful … also kinda slow and not elegant.
But what’s the bottom line? The bottom line is to get the job done–however you can responsibly get the job done.
https://ozdusoleil.com/wp-content/uploads/2018/10/excelguru-challenge-3-parsing-text-own-your-excel-style-youtube-thumbnail.jpg7201280Oz du Soleilhttps://ozdusoleil.com/wp-content/uploads/2017/01/oz-logo-v4-300x73.pngOz du Soleil2018-10-02 12:07:392020-11-03 09:42:25ExcelGuru Challenge #3: Parsing Text & Own Your Excel Style
Custom Functions in Excel’s Power Query. It’s finally time to take them on. For a while I couldn’t think of a good use. It’d seen the how-to but never really a what-for.
In this video I propose a common situation where lists will trickle in and we’ll need to convert the state abbreviations into state names via a master list. E.g., convert LA to Louisiana, and GU to Guam.
We’ll get one list, then 2 hours later another list, then tomorrow we’ll get 2 lists. Ongoing trickle. We could write a new VLOOKUP every time, but that would get tedious.
A Custom Function in Power Query will help us out.
I show how to do this as a partially manual process with 2 steps: 1. Manually bring the data into Power Query 2. Apply the custom function.
Pretty simple, but it takes some thinking. You’ll see some M Code and the “Insert Step After” feature.
https://ozdusoleil.com/wp-content/uploads/2018/09/custom-function-in-power-query-to-merge-2-tables-youtube-thumbnail.jpg7201280Oz du Soleilhttps://ozdusoleil.com/wp-content/uploads/2017/01/oz-logo-v4-300x73.pngOz du Soleil2018-09-12 22:05:572020-11-03 09:42:28Custom Function in Power Query to Merge 2 Tables
6 of us Excel MVPs came together to take on the challenge of making an integrated Excel solution by starting with 4 random Excel elements based on your suggestions. We have:
– MAX function – FREQUENCY function – Form Controls – 3D Model
See the other awesome entries from my fellow Excel MVPs in the playlist here: https://www.youtube.com/playlist?list=PLHrPHBbDHgT0UnY1qb36YZrBwpWNJX8Lx
And be sure to subscribe to their channels for more Excel education
– Leila Gharani, This Chick’s Trouble: Excel Hash
– Mr Excel, Bill Jelen, Will Bennu Impact Earth?
– Mike Girvin, ExcelIsFun, Max Consecutive Wins for Best City https://youtu.be/U1h2FrZFMqI – Mynda Treacy, My Online Training Hub, Interactive Bug Menu
– Jon Acampora, Excel Campus https://youtu.be/0ZW-h4kPlBI VOTE for your favorite entry here: https://tinyurl.com/y9hmvelp
My solution is called “Greatest Threat” The model allows users to set a high and low number, and one of 2 situations. The model then calculates the greatest threat. Example:
Situation A, between 8 and 96 Greatest Threat: Wasp de Fatal
Situation B, between 2 and 12 Greatest Threat: Ghost Wolf
Situation B, between 2 and 20 Greatest Threat: Vampire Earwig
https://ozdusoleil.com/wp-content/uploads/2018/09/excelhash-greatest-threat-max-frequency-form-controls-3d-model-youtube-thumbnail.jpg7201280Oz du Soleilhttps://ozdusoleil.com/wp-content/uploads/2017/01/oz-logo-v4-300x73.pngOz du Soleil2018-09-04 10:00:022020-11-03 09:42:32ExcelHash: Greatest Threat – MAX, FREQUENCY, Form Controls, 3D Model
