Practical use for Excel’s EXPAND function | VSTACK mystery solved | Dynamic Arrays

In my previous video I uncovered something odd about VSTACK. Turns out, VSTACK is doing what it’s supposed to do. I show that in the video. And that leads to something fascinating: EXPAND.

The EXPAND function on its own seems strange and pointless. However, in this situation EXPAND saves the day in a practical example.

We have a start date and number of days and want to list each date. Once their listed, we’d like them to be compiled in a single array. The problem is, the rows don’t have an even number of dates. That’s where EXPAND comes in! EXPAND makes all rows equal so that the data can be both, dynamic and compiled in a single array.

You’ll also see the TEXT function used to convert a number into a date.

#DynamicArrays #EXPAND #ExcelTips

For a list of my Excel courses at Lynda/LinkedIn:
https://www.linkedin.com/learning/instructors/oz-du-soleil
There are courses on Power Query, Good spreadsheet habits, and a weekly Excel challenge that comes out every Friday.

Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 3rd Edition
https://www.mrexcel.com/products/guerrilla-data-analysis-3rd-edition/
My old blog: http://datascopic.net/blog-2-2

Wyn Ticket Challenge

This video includes the weaving together of 7 dynamic array functions in the creation of a solution.

Wyn Hopkins, fellow Microsoft Excel MVP, presented a challenge recently:
If you have a list of people and their ticket numbers:
Lisa Tickets: 300-303
KC Tickets 390-390
Aaron Tickets 772-778
How can you get a list of the individual ticket numbers alongside the name of the ticket holders? E.g. 4 rows for Lisa with tickets 300, 301, 302, 303.

I show you a solution using:
FILTER, SEQUENCE, TOCOL, TEXTBEFORE, TEXTAFTER, HSTACK, VSTACK

For a list of my Excel courses at Lynda/LinkedIn:
https://www.linkedin.com/learning/instructors/oz-du-soleil
There are courses on Power Query, Good spreadsheet habits, and a weekly Excel challenge that comes out every Friday.

Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 3rd Edition
https://www.mrexcel.com/products/guerrilla-data-analysis-3rd-edition/
My old blog: http://datascopic.net/blog-2-2

Power Query: Sorting & Merging and ignoring Case-Sensitivity | Excel Days in Bulgaria | My Roadtrip

Power Query does some odd things because it’s so case-sensitive.

If you want to sort this list:
iPhone
Ice Cream
USB cable

Power Query will return:
Ice Cream
USB Cable
iPhone

If you need to do a merge with:
De Soto and de Soto
Power Query will not get those matched.

In this video I show you how to use Text.Upper to get the sorting right and Fuzzy Matching and Similarity Threshold to get the merge right.

Thank you to Ed Hansberry for his blogpost that shows details on merging and ignoring case-sensitivity:
https://www.ehansalytics.com/blog/2020/4/27/case-insensitive-merges-in-power-query
Plus! I share with you where I’ve been for the past month and invite you to Excel Days in Bulgaria on 11NOV22.

0:00 Introduction
0:15 Roadtrip Overview
1:05 Excel Days in Sofia Bulgaria
3:03 Power Query: sorting without case-sensitivity
7:16 Power Query: merging and ignoring case-sensitivity

For a list of my Excel courses at Lynda/LinkedIn:
https://www.linkedin.com/learning/instructors/oz-du-soleil
There are courses on Power Query, good spreadsheet habits, and Excel challenges that comes out every Friday for beginners and every-other-Monday for power users.

Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 3rd Edition
https://www.mrexcel.com/products/guerrilla-data-analysis-3rd-edition/
My old blog: http://datascopic.net/blog-2-2

Excel’s New IMAGE Function: the how-to and the gotchas

There’s a new function in Excel–a function that we’ve been waiting and hoping for for a long time.

The IMAGE function.

IMAGE makes it easy to bring images into Excel and use them as variables. However, you can count on me to not only show you how Excel features work, but also issue warning. This time, the thing to worry about: Link Rot.

And here’s where you can download the file: https://datascopic.net/imagef

For a list of my Excel courses at Lynda/LinkedIn:
https://www.linkedin.com/learning/instructors/oz-du-soleil
There are courses on Power Query, Good spreadsheet habits, and a weekly Excel challenge that comes out every Friday.

Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 3rd Edition
https://www.mrexcel.com/products/guerrilla-data-analysis-3rd-edition/
My old blog: http://datascopic.net/blog-2-2

Creativity and Video Editing in Camtasia

I’ve received a lot of questions about
– Editing techniques in Camtasia
– Where my creative ideas come from

In this video I take you on a tour of a recent video. Specifically, this one on TEXTBEFORE:

Let me know if you have any questions about anything in this video.

#Camtasia
#Camtasia2022
#videoeditingtricks

For a list of my Excel courses at Lynda/LinkedIn:
https://www.linkedin.com/learning/instructors/oz-du-soleil
There are courses on Power Query, Good spreadsheet habits, and a weekly Excel challenge that comes out every Friday.

Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 3rd Edition
https://www.mrexcel.com/products/guerrilla-data-analysis-3rd-edition/
My old blog: http://datascopic.net/blog-2-2

DataRails and The Functionary: Excel Top 5 Wall of Shame

For a list of my Excel courses at Lynda/LinkedIn:
https://www.linkedin.com/learning/instructors/oz-du-soleil
There are courses on Power Query, Good spreadsheet habits, and a weekly Excel challenge that comes out every Friday.

Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 3rd Edition
https://www.mrexcel.com/products/guerrilla-data-analysis-3rd-edition/
My old blog: http://datascopic.net/blog-2-2

Create Variable Columns in Power Query – Goodly Challenge – The First Class Edition

Here is an interesting challenge. With a column of names, and a column of musical preferences and each person has multiple music preferences, how can the preferences be split such that you have TRUE if a person has that preference, and FALSE if a person does not?

One twist: what if there’s a person added/removed or a music preference is added/removed?

This challenge came from Chandeep Chhabra at the Goodly YouTube channel.
My solution in Power Query includes:
Left Outer Join with 2 criteria
Cross Join
Conditional Column
Pivot Don’t Aggregate

All handy features that can help you create dynamic solutions in Excel and Power Query.

See the challenge as described by Chandeep: https://youtu.be/7Vow1L8Mu9g

0:00 Intro
0:54 Explaining the challenge
2:15 Starting the solution
4:39 Cross Join
6:34 Merge queries with 2 criteria
8:29 Pivot Don’t Aggregate
9:50 Add more data
10:49 Outro

#ExcelChallenge #FirstClass #PowerQuery

For a list of my Excel courses at Lynda/LinkedIn:
https://www.linkedin.com/learning/instructors/oz-du-soleil
There are courses on Power Query, Good spreadsheet habits, and a weekly Excel challenge that comes out every Friday.

Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 3rd Edition
https://www.mrexcel.com/products/guerrilla-data-analysis-3rd-edition/
My old blog: http://datascopic.net/blog-2-2

Excel Campus License Plate Challenge: The Down & Dirty Edition

Jon Acampora of Excel Campus issued a challenge to designate if a license plate is odd or even. In a previous video I showed a Power Query challenge. In this video I show a quick solution that will work, BUT, you have to be wary.

This solution isn’t so sophisticated but it’s worth showing you because sometimes all you need is a down-&-dirty one-time solution. Other times, you do need a solution that is robust, dynamic, and future-proof.

In this video I use Flash Fill, ISODD and IF. They work!

My book: Guerrilla Data Analysis 3rd Edition
https://www.mrexcel.com/products/guerrilla-data-analysis-3rd-edition/
#ExcelChallenge
#ExcelOnFire

For a list of my Excel courses at Lynda/LinkedIn:
https://www.linkedin.com/learning/instructors/oz-du-soleil
There are courses on Power Query, Good spreadsheet habits, and a weekly Excel challenge that comes out every Friday.

Website: https://ozdusoleil.com
My old blog: http://datascopic.net/blog-2-2

Excel Campus License Plate Challenge: Odd vs. Even Days & License Plates

Jon Acampora of Excel Campus has an interesting challenge: https://youtu.be/-ByG6ULWOtA
Yellowstone National Park has a system where cars with even number license plates can come into the park on even numbered days. Odd number plates can enter on odd dates.

But what if a license plate doesn’t end with a number? (4CE-3DQ)
What if it doesn’t have any numbers? (BIG-DAY)

In this video I show a Power Query solution that uses the Information feature and the Text.Remove function.

For a list of my Excel courses at Lynda/LinkedIn:
https://www.linkedin.com/learning/instructors/oz-du-soleil
There are courses on Power Query, Good spreadsheet habits, and a weekly Excel challenge that comes out every Friday.

Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

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