ExcelGuru Challenge #3: Parsing Text & Own Your Excel Style

/in /by

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.

The ExcelGuru Challenge No. 3 solutions are here:

Power Query Challenge 3 Results

Jerome Bettis Highlights

#ExcelChallenge
#PowerQuery
ExcelGuruChallenge

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
Download the workbook: http://datascopic.net/EGC3

Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition
http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336
My old blog: http://datascopic.net/blog-2-2

Custom Function in Power Query to Merge 2 Tables

/in /by

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.

#PowerQueryCustomFunction
#CustomFunctionInPowerQuery
#MergeTablesInPowerQuery

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
Download the workbook:
http://datascopic.net/CFMerge
Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition
http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336
My old blog: http://datascopic.net/blog-2-2

ExcelHash: Greatest Threat – MAX, FREQUENCY, Form Controls, 3D Model

/in /by

ExcelHash is here!!

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

Download the ExcelHash Greatest Threat workbook:
https://1drv.ms/f/s!Aoud6IXCTuoDryeEwn5p6e6Y3Kza
#ExcelHash
#ExcelChallenge
#ExcelFormControls

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
http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336
My old blog: http://datascopic.net/blog-2-2

VLOOKUP vs INDEX/MATCH: The Hottest Debate in Excel

/in /by

Today I had to formally weigh in on the debate between VLOOKUP and INDEX/MATCH. This argument has gone on for years, and last week I heard strains of it rising up again. So, here’s what I say:

VLOOKUP is a tool. INDEX/MATCH is a tool. VLOOKUP has weaknesses, e.g., it’s easy to break, and it can’t do lookups to the left. Conversely, INDEX/MATCH is undoubtedly more robust. But it’s actually a function nested inside another function. That makes it awkward to work with.

They’re both legitimate choices. The problem comes up with the INDEX/MATCH zealots who call for the Excel developers at Microsoft to eliminate VLOOKUP. They are proud to post things like, “I haven’t used VLOOKUP in 10 years” and insist that VLOOKUP shouldn’t be taught to beginners because it’s a bad habit.

Basically, they’ve turned INDEX/MATCH into a religion … and they’re viciously recruiting new members.

My video makes 3 points:
1. VLOOKUP and INDEX/MATCH are tools. Period.
2. Your own context dictates which tool to use.
3. Don’t listen to the INDEX/MATCH insurgents. BOOO!

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
http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336
My old blog: http://datascopic.net/blog-2-2

Excel, COUNTIFS, Wildcards and the Most Popular Trio of Pizza Toppings

/in /by

Of more than 200 pizza orders and 20 ingredients, which trio of toppings shows up the most?

This was more of a mind exercise because the Excel piece was easier than I thought. One challenge is that we want which 3 show up together the most–even if they are with other items. Example:

3 pizzas:
– Extra cheese, mushrooms, grilled onions
– Ham, extra cheese, black olives, grilled onions, mushrooms
– Mushrooms, pepperoni, grilled onions, extra cheese

These all count toward the trio of: extra cheese, mushrooms, grilled onions. We need Excel to see that.

The first step is to get a list of all the possible combinations of 3 toppings:

Out of 20 items, there are 1140 combinations of 3.

Thanks to the MathiIsFun website, we can get a list of all 1140 combinations.
https://www.mathsisfun.com/combinatorics/combinations-permutations-calculator.html
Then we need Excel wildcards and COUNTIFS to tally how many times each of the 1140 trios was ordered.

#CombinationsAndPermutationsInExcel
#COUNTIFS
#ExcelWildcards

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
http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336
My old blog: http://datascopic.net/blog-2-2

Ken Puls ExcelGuru Challenge: Isolate Members and Memberships

/in /by

Recently, Ken Puls of ExcelGuru.ca issued a challenge:
Isolate the members who have more than one type of membership and show all of their transactions.

This was tricky for a few reasons:
– One person had 2 transactions but for the same type of membership. We DON’T want that person in the final list.
– One person had 4 transactions for 3 types of memberships. We want to show all 4 transactions

This video shows my solution. You’ll see GroupBy, a Left Outer Join, and evvythang!

#ExcelChallenge
#GroupingInPowerQuery
#PowerQueryGroupBy

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
http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336
My old blog: http://datascopic.net/blog-2-2

#ExcelHash: Your Suggestions are Needed

/in /by

#ExcelHash is a game we’re going to try out and you get to suggest the 4 ingredients.

ExcelIsFun
MrExcel.com
Leila Gharani
MyOnlineTrainingHub
Excel Campus

and I are going to build something based on your suggestions. It might be useful, it might be patently useless. The goal is to create something integrated that does SOMETHING. Anything. 😀

Suggestions so far:
XOR
A picture of a lizard
Slicers
BESSELL
Data Consolidate

The 4 ingredients will be chosen at random on 6AUG.
Please. Leave your suggestions in the comment section or use
#ExcelHash on Twitter.

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
http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336
My old blog: http://datascopic.net/blog-2-2

Use Power Query to Identify 2 Consecutive Months of Underperformance

/in /by

If performance is below standards for 2 consecutive months, something bad is going to happen!

Highlighting periods like that are challenging in Power Query because PQ forces us to think in terms of whole columns or whole tables. We can’t easily compare a range of cells against each other and pluck out the offenders.

This is based on a real project where a client could not bill for periods where they fell short of their promise for 2 consecutive months.

In this video I show a solution that includes merging tables, Unpivot, adding custom columns, and adding conditional columns. As an extra step, I make the threshold variable by using Power Query’s Drill Down and a little massagin

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
http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336
My old blog: http://datascopic.net/blog-2-2

A Case Study on Data Quality: Find & Clean Skunk Data

/in /by

Too often I see spreadsheets that have models built, complex formulas, etc. The user will come to me and ask for help with automation. But I often discover data quality issues that cause us to back up and assess everything.

I was helping someone with his project. Lots of data in multiple workbooks, summary pages, hidden sheets everywhere, fine details … just wild!

I slowed things down so that we could check the data quality. WOAH! That’s when my client observed how people were making additional rows of data to make amplifying details. Example:

Row1: Food/Catering
Row2: Coffee never arrived

Our goal was to count the Food/Catering problem. But, we have TWO rows … DOUBLING the number of problems that we really have. We don’t want that detail in Row2.

This video summarizes the situation. We look at the data using pivot tables. We then use tables and a Power Query inner join to merge the tables and isolate the data that we want to keep.

We don’t spend a lot of time on the Excel stuff. The focus here is on the importance of knowing your data and keeping data quality on your mind at all times.

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
http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336
My old blog: http://datascopic.net/blog-2-2