This video is based on a solution that Alan at Computergaga solved by using Data Validation (see his video: https://youtu.be/1GDgTN4lvAo) Of course, I started thinking: Dynamic Arrays. The challenge: How can I flag a row that has duplicate entries? My solution doesn’t use any of the new functions (e.g., FILTER, RANDARRAY, SEQUENCE, etc.). Instead, it […]
This video shows 2 examples of messy data and how they’re handled by Excel’s Flash Fill and Power Query’s Column by Example. We have to merge first names and last names but also, deal with all caps, lower case, and get the names into proper case, but also there are prefixes like ‘Rev’ that we […]
Lordy! This is a good one! Just within the past week the RANDARRAY function was improved! Dynamic Arrays, the functions and the new calculation engine just keep getting better. RANDARRAY. WOW! This is better than both the old RAND and RANDBETWEEN functions! This video shows 2 examples of using RANDARRAY. In the olden days, we […]
You’ve seen the Dynamic Array videos. Now, it’s time for a quiz. 6 questions to test your fitness for battle in a world where: Cleansing data is brutal The consequences are real, and Crap Data is heartless To help: Here is a playlist of Dynamic Array videos from other YouTubers: For an intro to Get […]
Here we are again! I was playing around more with the Dynamic Array functions, especially after Leila Gharani posted her video that made unstacking data pretty easy with a substitution trick and finding a pattern in the dataset. Here is her video: Well, I found a 2-step process that REALLY simplifies unstacking a column of […]
Excel’s Dynamic Arrays functions are a real thrill! In this video I show the FILTER function and how to think through the logic of what it’s doing. Camtasia Course video editing course https://oz-s-school.thinkific.com/ Course discount code for the first 25 people: ny2019 For an intro to Get & Transform (Power Query) try my Lynda/LinkedIn course: […]
Excel’s Dynamic Arrays are fascinating … and complex. Here is a second video where I dig into the logic of the FILTER function. You’ll see examples of the FILTER function, filtering on 2 criteria where I play with the logical operators: Greater Than Less Than Equal To Not Equal To Smaller Than or Equal To […]
The new FILTER function is fantastic, but I wondered how can I make an optional field? Sometimes we do searches and want to look at items between $10 and $100 for brand X. We also want to look at everything between $10 and $100 regardless of the brand. That’s what I wanted to replicate with […]
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! For […]