This is real! A non-profit needs to determine which tax receipts have NOT been issued. This is easy to figure out if each donor made one donation. But it’s harder when: – The same person donates multiple times AND – They donate the same amount multiple times. Anne donated $150, $150 and $95. Her receipt […]
You’ve seen the Joins in other videos, but here is a fun overview of all 6 joins in Excel’s Get & Transform (Power Query) TIMELINE 2:39 Creating Queries 3:47 Full Outer Join 6:00 Left Outer Join 7:18 Right Outer Join 8:45 Inner Join 9:51 Right Anti Join 11:24 Left Anti Join 13:06 Something Crazy Website: […]
WARNING: this is a fast-paced overview. Don’t worry if you can’t follow every step. The detailed workbook can be downloaded here: http://datascopic.net/3Stores8Segments 0:29 Overview 2:35 Demonstration of the solution 3:17 How the solution/model works 3:45 Figuring out who HAS NOT made any purchases 4:30 Who’s made purchases at all 3 stores? 7:04 Figuring out who […]
This is cool! The problem: 11 worksheets and hundreds of codes Objective: the ability to type a code and be taken directly to that code wherever it is in the workbook. It’s like the Find/Select feature in Excel, the user wanted to stay in the worksheet and minimize use of the ribbon. One thing to […]
This is a CRAZY one! Kevin Lehrbass presented this problem: A tall stack of mini data ranges that need to be totaled. BUT! Only total the ranges that say “TOTAL.” Ignore the ranges that are labeled anything else. (Kevin’s solution: https://www.youtube.com/watch?v=oomf8bfcFEw) Kevin’s video shows various formula solutions. My video shows a Power Query (Get and […]
Lately, there have been a number of requests for stacking data that’s in multiple columns. This video shows 2 methods for stacking 3 columns of data: – The INDIRECT formula can be used once we know the pattern of how the data needs to be rearranged. – In Get & Transform (Power Query) it’s simple: […]
This was interesting, and the solution is sweet. We’ve got headers and a large number of paired columns that need to be condensed down to just 2 columns. This was a real challenge that was recently presented to me. It was overwhelming, and I searched for existing solutions. The search turned up complex methods that […]
NOTE: This video isn’t about the details. It’s the thought process. So, please don’t worry if you don’t catch everything. Download here: http://datascopic.net/messiest Subscriber, eCabinets Tips and Tricks asked about the most bizarre spreadsheet I’ve ever faced, and what I did to untangle it. ‘Bizarre’ can be measured a lot of ways. I chose this […]
Here’s a quick video correcting 2 things from the previous video: Import Data from Web Page and Clear Line-Breaks Thanks: Wyn Hopkins, Micheal Reynolds and Chris Pope for pointing these out: 1. There was a single carat delimiter that I missed 2. There’s a way in Power Query (Get and Transform) to separate columns by […]