Excel Challenge (Kevin Lehrbass): Get & Transform to List All Unique Pairs in a Class of 10 Students

/in /by

Once again, Kevin Lehrbass (MySpreadsheetLab) and I are taking on a common challenge and comparing each others’ solutions.

Today’s challenge: if you have 10 students in a class, and would like each student to work with a different student each week, how can you get a list of all of the unique pairs?

My favorite solution that Kevin created uses pivot tables and helper columns. Check out the details of Kevin’s solutions here:

Kevin’s blogpost

Video 00153 All Possible Pairs from List of Names

I present 2 solutions:
1. A Matrix solution that pairs each student.
2. Get & Transform. This uses a cross-join aka Cartesian-join and it’s beautiful! I also show how easy it is to get the unique pairs from 22 students.

This video takes you through a detailed approach to solving this problem in Excel.

ModelOff Global Training Camp – Toronto

Toronto GTC

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

http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336

Announcement: Free Data-Cleansing & Power Query Training

/in /by

FREE EXCEL ONLINE TRAINING (THIS WEEK ONLY): Power Query & Data Cleansing Techniques!

SIGN UP HERE: http://myexcelonline.com/blog/datacleansingsignup

~ Receive 3 FREE training videos that will show you how to save HOURS EACH DAY using Excel’s built in features…including Formulas, Text to Columns, Pivot Tables plus much more!

~Learn the most powerful feature in Excel since VLOOKUP…it’s called POWER QUERY & it will rock your Excel world!!!!!

~ Transform messy data & automate your daily/weekly/monthly reports within minutes (NOT DAYS!)…without using VBA or Macros!

LEARN NOW: http://myexcelonline.com/blog/datacleansingsignup

My book: Guerrilla Data Analysis 2nd Edition

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

https://www.youtube.com/user/bjele123

Wanderlust Travel Tag | Oz du Soleil

/in /by

Taking you for a drive around Portland, OR while I answer 10 questions about travel and I’ve tagged:

Mr Excel, Bill Jelen and

St. Lucian vocalist, Sherwin Dupes Brice

https://www.youtube.com/channel/UCDJWTHHxVpOBmyTwuLYkd1w
I also mention my favorite sriracha:
Ghost Pepper Sriracha from California Blazin’ Chile Farms (I am not compensated for this shout-out, at all)
https://www.etsy.com/listing/165113213/blazing-dragon-ghost-pepper-sriracha
Amy Schmittauer’s Wanderlust Tag Video

And here are the questions!
WANDERLUST TRAVEL TAG QUESTIONS:
1. Your most treasured passport stamp?
2. Can you recite your passport # from memory if asked?
3. Preferred method of travel; planes, trains or automobiles?
4. Top 3 travel items?
5. Hostel or hotel?
6. Are you a repeat visitor or do you explore new places?
7. Do you read up on your destination (culture, history, safety) or do you wing it?
8. Favorite travel website?
9. Where would you recommend a friend to visit? Name city & why.
10, You’re leaving tomorrow, money is no object, where are you going?

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

Excel Ignited Day 16 The Ultimate Doggone VLOOKUP Tutorial

/in /by

I never expected to do a VLOOKUP tutorial because there are so many that other excellent folks have done. But … it’s time.

In this video:
– When to use VLOOKUP 0:50
– VLOOKUP’s pieces 1:45
– Raw VLOOKUP 3:52
– VLOOKUP with Tables 10:23
– Flash Fill 8:39
– weaknesses and Controversies 12:18

I focus on VLOOKUP with False (exact match).
VLOOKUP with True is wrongly described as an approximate match. No! VLOOKUP True is for use when records need to be assigned to tiers or categories. For more on VLOOKUP/True:

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

Excel Ignited Day 15: Tables, Slicers, VLOOKUP

/in /by

Revisiting Excel Tables!
Using tables is a good habit to get into. Tables allow your spreadsheets to flex easily, and ease your worries about delicate formulas that might break or miss important data.

This video goes deeper into tables than the Day 11 video did.

Here, you’ll see the Table nomenclature; how Tables compare to data ranges; use of Slicers; VLOOKUP, and more Pivot Tables.

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

Excel’s IF, AND, and OR Functions | Excel Ignited Day 14 | SSSDEVA

/in /by

This video picks up exactly where Day 13 stopped. Here’s that video:

Day 13 introduced the AND and OR functions. This video

– Nests AND inside IF to test for True and False
– Compares cell values to see if an exam was submitted within the time allotted.

Excel Ignited is a video series for Excel beginners. This was supposed to have been a series of videos posted daily during August 2016. However, once started … WOAH! The second video took 10 hours to record and edit. So, rather than daily videos, this will be a 31-episode video series that may take until the end of September to complete.

You with me?

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

http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336

Excel’s AND and OR functions | Excel Ignited Day 13 | SSSVEDA

/in /by

Continuing the series for Excel beginners, this video shows the usefulness of the functions AND and OR.

My book: Guerrilla Data Analysis 2nd Edition

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

Combine Multiple Worksheets Using Power Query

FunkyFunktionFriday – Consolidating 7 Worksheets using Simple M Code (The Big Payback)

/in /by

This was a real question that came to me. Someone has 113 worksheets in a single Excel file. How can he get all of the data stacked up in a single worksheet?

Here, I show how to consolidate the data on 7 worksheets. For 113, the process would be the same.

Thanks to Ken Puls for sharing this solution in a blogpost

This solution uses M Code in Excel’s Get & Transform. This is best because G&T does have a manual way of consolidating worksheets, and it’s fine if there are just a few worksheets because there’s a step that needs to be repeated for each worksheet. But no! Let’s not do that 113 times.

Using M code … we got it licked!

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

http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336

What does it take to get data into a Pivot Table? | Excel Ignited Day 12 | SSSVEDA

/in /by

In a previous video I showed pivot tavles and said that they big problem with pivot tables isn’t the pivot table; it’s having the data being ready to go into the pivot table.

In this video I list some features and concepts that make source data pivot table ready.

The first is the data must be contiguous: no empty rows or columns. This also means getting rid of summaries and subtotals.

Next. All columns must have headers, and those headers need to reflect the possible ways that you might want to look at your data. If you want to look at regions, you need a ‘regions’ column header.

An important concept: sweep all of your data together into as few places as possible, Excel can peel down what you want to see.

If you have expenses and income and want a monthly view. Don’t make 24 different worksheets. Instead, make just TWO sheets, Income and Expenses. And let Excel peel out the monthly views using pivot tables and other Excel features.

My book: Guerrilla Data Analysis 2nd Edition

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