The New & Improved RANDARRAY Dynamic Array Function

/in /by

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 would have needed more than just this one function. You will also see RANDARRAY used with INDEX and COUNTA! Everything you possibly could need … it’s all here, brothers and sisters. It’s all here, and it’s FIRE!

#DynamicArrays
#RANDARRAY
#ExcelFunctions
For more Excel and an intro to Get & Transform (Power Query) see my Lynda/LinkedIn courses:
https://www.linkedin.com/learning/instructors/oz-du-soleil?trk=insiders_28299411_learning
Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

My old blog: http://datascopic.net/blog-2-2

6-Question Quiz on Dynamic Arrays

/in /by

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 & Transform (Power Query) try my Lynda/LinkedIn course:
https://www.linkedin.com/learning/instructors/oz-du-soleil?trk=insiders_28299411_learning
Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

My old blog: http://datascopic.net/blog-2-2

Dynamic Array: Unstack Data with SEQUENCE … just 2 steps

/in /by

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 data. SEQUENCE, COUNTA and INDEX get us there, taking advantage of spilled arrays and Excel’s new calculation engine. Alas, not everyone has the Dynamic Arrays yet. If you don’t have them, and you need to unstack some data, use Leila’s method. 👍

For an intro to Get & Transform (Power Query) try my Lynda/LinkedIn course:
https://www.linkedin.com/learning/instructors/oz-du-soleil?trk=insiders_28299411_learning
Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

My old blog: http://datascopic.net/blog-2-2

Excel Dynamic Arrays: Filter for 3 Criteria

/in /by

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:
https://www.linkedin.com/learning/instructors/oz-du-soleil?trk=insiders_28299411_learning
Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

My old blog: http://datascopic.net/blog-2-2

Excel Dynamic Arrays: 4 Complex Filter Examples

/in /by

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

For an intro to Get & Transform (Power Query) try my Lynda/LinkedIn course:
https://www.linkedin.com/learning/instructors/oz-du-soleil?trk=insiders_28299411_learning
Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

My old blog: http://datascopic.net/blog-2-2

Excel Dynamic Arrays: FILTER for Optional and Required Fields

/in /by

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 FILTER function: an optional filter. The straight-forward FILTER function forces us to filter for something whether we want to, or not.

My solution involves using an IF statement and smart use of the # nomenclature. Check it out!

#DynamicArrayFunctions #OptionalFilter

For an intro to Get & Transform (Power Query) try my Lynda/LinkedIn course:
https://www.linkedin.com/learning/instructors/oz-du-soleil?trk=insiders_28299411_learning
Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

My old blog: http://datascopic.net/blog-2-2

Excel Dynamic Arrays: Unstack a Column of Records

/in /by

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 an intro to Get & Transform (Power Query) try my Lynda/LinkedIn course:
https://www.linkedin.com/learning/instructors/oz-du-soleil?trk=insiders_28299411_learning
Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

My old blog: http://datascopic.net/blog-2-2

Power Query: Keep all Capital Letters with Text.Select in M-Code

/in /by

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.

For an intro to Get & Transform (Power Query) try my Lynda/LinkedIn course:
https://www.linkedin.com/learning/instructors/oz-du-soleil?trk=insiders_28299411_learning
Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

My old blog: http://datascopic.net/blog-2-2

Excel Dynamic Arrays: 6 Examples of FILTER with Multiple Criteria

/in /by

Excel’s new Dynamic Array functions are trippy! They require a whole new way of thinking.

In this video I play with the FILTER function just to see what it does. You’ll see 6 examples of complex, multi-conditional, multiple column formulas.

For an intro to Get & Transform (Power Query) try my Lynda/LinkedIn course:
https://www.linkedin.com/learning/instructors/oz-du-soleil?trk=insiders_28299411_learning
Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

My old blog: http://datascopic.net/blog-2-2