Video 00091 Remove Duplicates with a twist Kevin Lehrbass 2026-09-28 | Video 00091 Remove Duplicates with a twist
00182 Partial Match POWER QUERY Kevin Lehrbass 2019-05-17 | Count rows based on conditions. 0:00 Requirements 0:24 Load Data into Power Query load 3 tables 1:24 Requirement Adjustment ‘lamp’ or ‘lamps’ NOT ‘clamps’ 1:56 Prep text wrap with spaces, text to lowercase 3:34 Merge tables filters 10000 rows to selected state 4:36 Add SearchWord repeat search words beside rows 5:50 Search words found? function Text.PositionOf 6:39 Filter out rows if search words not found remove rows 7:26 Export rows to Excel sheet 7:44 Create Row Count query group by query counts rows 9:11 Export Row Count query to Excel sheet In a previous post I used formulas to solve this: myspreadsheetlab.com/partial-match-count-with-a-condition
00181 Extract largest number inside a cell Kevin Lehrbass 2019-03-03 | This was a challenge from a work colleague. I love this array solution!Do you have another solution? Note that although the array is a bit heavy there are no slow volatile functions used (i.e. indirect). My blog post: myspreadsheetlab.com/largest-number-inside-alphanumeric-stringmyspreadsheetlab.com/extract-largest-number-power-query-flash-fill-solutions
00180 Data Disaster GET & TRANSFORM solution Kevin Lehrbass 2018-07-18 | myspreadsheetlab.com/normalize-data-using-excel-get-transform00:00 Intro00:26 Data layout disaster01:03 Insert table01:22 Add helper columns-------------------------GET & TRANSFORM02:08 Load table02:42 Normalize data03:27 '2ManagerNames' only04:01 3FINALOUTPUT employees only04:44 Merge 3FINALOUTPUT & 2ManagerNames06:47 Export 3FINALOUTPUT to tableExiting GET & TRANSFORM-------------------------07:13 Finally...our simple vlookup!*****************************MY POWER QUERY JOURNEY !www.myspreadsheetlab.com/learning-power-query-get-transform*****************************
00179 Data Disaster non normalized data Kevin Lehrbass 2018-07-15 | Our weekly data isn't in the correct layout. We can't do a simple vlookup!myspreadsheetlab.com/data-disaster-non-normalized-data-in-excel00:00 challenge explanation01:00 our options02:19 Step1 create named range02:45 Step2 verify assumption03:36 Step3 which column?04:50 Step4 which row?05:31 Step5 binning table06:07 Step6 manager row07:55 Step7 employee's manager!
00178 Retrieve Code Based on 2 Criteria Kevin Lehrbass 2018-05-16 | 1st code for selected city when description not blank. Leave a comment to vote for your favorite solution.myspreadsheetlab.com/retrieve-1st-code-for-selected-city-when-description-is-not-blank0:00 Intro1:10 (a) vlookup array3:01 (b) index & match array4:29 (c) lookup5:18 (d) helper & vlookup6:03 (e) helper & vlookup (2)6:50 (f) helper & index match7:24 (g) index & array
00177 Variable Sized Groups In Each Column Kevin Lehrbass 2018-05-07 | A fun solution to a unique request. My post:myspreadsheetlab.com/variable-sized-groups-within-each-column-of-data1) Highlight top x values per column2) Highlight group green if x % of group numbers are in the top x %3) Functionality to switch off (1) & (2) above4) Solution must work with additional columns of dataConditional formatting in Excel is so powerful and most of the time the rules are easy to create.
00176 Normalize & Pivot Taco Orders Kevin Lehrbass 2018-04-12 | www.myspreadsheetlab.com/excel-pivot-tables-analyze-taco-data00:00 Tacombi.com tacos!!00:14 Menu orders not normalized00:46 Our scenario (analyze the data)01:22 What we need to do (before pivot)02:42 Solution A array formula etc04:16 Solution B helper formulas06:11 Solution C Get & Transform07:31 Solution D quick and dirty08:12 Pivot Table samples08:41 Learn Pivot Tables, Get & Transform
00175 YouTube Transcript Excel Template Kevin Lehrbass 2018-03-06 | Get template here: myspreadsheetlab.com/youtube-video-transcript-excel-template
00173 Dynamically Combine Data from Excel Tables Kevin Lehrbass 2018-02-01 | http://www.myspreadsheetlab.com/video-00173-dynamically-combine-data-from-excel-tablesYes it's possible to dynamically combine data from Excel tables. BUT it's ridiculously difficult. So much easier with Get & Transform (Power Query) !
00174 Ranking Values into Tiers Kevin Lehrbass 2018-01-28 | 00:00 Intro/Requirements00:23 Step1 individual number RANK01:38 Step2 approx MATCH into 12 groups03:23 Verify results using COUNTIF04:05 Change array constant into a normal range of numbers in cells03:23 Verify Results: Groups of 12 numbers?http://www.myspreadsheetlab.com/video-00174-ranking-values-into-tiersreddit.com/r/learnexcel/comments/7hqi9k/ranking_into_tiers
00172 Unique Values for Drop Down List Kevin Lehrbass 2018-01-17 | myspreadsheetlab.com/video-00172-unique-values-for-data-validation-drop-down00:00 Requirement: drop down list with unique values01:01 light/simple/dynamic: pick 2--------------SOLUTIONS----------------------01:33 Mr Excel: Pivot Table02:41 Oz du Soleil: Power Query04:18 Mike Girvin: array formula05:30 Leila Gharani: formula06:17 Kevin Lehrbass: helper formulas09:25 Mike Rempel: auto-complete, pick from drop downLink to Leila's video: youtu.be/7fYlWeMQ6L8
00171 Create Christmas Lights Using Excel Sheets Kevin Lehrbass 2018-01-05 | Was I too late for a Christmas post? Jerry Seinfeld gives advice:http://www.myspreadsheetlab.com/video-00171-create-christmas-lights-using-excel-sheets
00170 Bill Szyszs Solution to Debs Challenge Kevin Lehrbass 2017-11-12 | 00:00 Bill Szysz solution (no search function!)00:37 Step 1 Hack the match function01:42 Step 2 Look for a 1 inside of Step 102:28 Step 3 Index gives us the answer03:10 Compare solutions (Deb Kevin Ankur Bill)Here's my blog post comparing various solutions! http://www.myspreadsheetlab.com/look-for-keywords-inside-of-a-column-of-text-values
00169 Ankur Shuklas Solution to Debs Challenge Kevin Lehrbass 2017-11-12 | 00:00 Ankur's Lookup & Search solution00:39 Review of requirements02:23 Deb's solution03:04 Step 1 search array03:55 Step 2 1 divided by search array results05:10 Step 3 search for a 2 (we'll never find)05:35 *key concept* Binning09:10 Step 4 [result_vector] displays answerOriginal idea came from Deb's Spreadsheet day challengeVisit Deb's website and youtube: http://www.debradalgleish.comyoutube.com/user/contexturesHere's my blog post comparing various solutions! http://www.myspreadsheetlab.com/look-for-keywords-inside-of-a-column-of-text-values
00168 Debs Spreadsheet Day Formula Challenge Kevin Lehrbass 2017-10-19 | Visit Deb's amazing websites: http://www.debradalgleish.comyoutube.com/user/contexturesDEB's ARRAY FORMULA SOLUTION00:00 Formula puzzle explanation01:19 Step 1 Look for each 'Customer'02:33 Step 2 Replace errors with zero02:58 Step 3 Look for a 1 inside the array04:48 Step 4 Index gets the 'Code' answer05:38 My alternative array solution...I've learned SO much from Deb over the years. Thanks and Happy Spreadsheet Day!Here's my blog post comparing various solutions! http://www.myspreadsheetlab.com/look-for-keywords-inside-of-a-column-of-text-values
00167 Sliding Formula Ranges Kevin Lehrbass 2017-10-02 | This concept could save you a lot of time!Get Excel file here: http://1drv.ms/1bYwrTa00:00 3 sloppy datasets01:10 Should we always normalize data?02:29 Formulas with sliding ranges!?!?03:34 Sliding range solution 105:26 Sliding range solution 206:20 Sliding range solution 3
00166 Count Groups Above or Below Targets Kevin Lehrbass 2017-09-24 | How many groups or clusters of numbers are above or below target values?Get Excel file here: http://1drv.ms/1bYwrTa00:00 Requirements01:14 How much data? What is file size?02:17 Visualize what we want to build02:34 Step1 Get started using AND function02:47 Step2 Flag TRUE once per group (above)03:36 Step3 Flag TRUE once per group (below)04:08 Step4 All-In-One formula
00165 Show Col Header for Matrix Value (Leilas Challenge) Kevin Lehrbass 2017-08-20 | Get Excel file here: http://1drv.ms/1bYwrTahttp://www.myspreadsheetlab.com/video-00165-show-column-header-for-matrix-valueLook for text value in non normalized data, return column header.00:00 Intro00:14 Requirements01:17 Factors to consider02:08 Solution Comparison Grid02:25 SOLUTION: Sumproduct or Array03:06 SOLUTION: Simple helper formulas06:38 SOLUTION: Normalize, index/match------------------------------------Update: I added Get & Transform solution in the Excel file.Leila Gharani's original video: youtu.be/OJLfPc9YlqELeila Gharani's website: http://www.xelplus.comOz du Soleil's solution: youtu.be/IwBYEXaOSOkMy blog: http://www.myspreadsheetlab.com/blog
00164 Excel Magic Trick 1452 Max Monthly Customer Kevin Lehrbass 2017-08-08 | See how to calculate the single largest Customer Monthly Total from 81 customers over a 12-month period.Alternative solution to Mike Girvin's solutions.00:00 Challenge review00:26 Mike's solutions!01:30 My solution: dynamic & easy to explain/audit01:48 Step 1: Helper column to truncate transaction date02:10 Step 2: Helper column for every month02:41 Step 3: Helper columns to calc sales per month & customer03:33 Step 4: Calc the max of Step 3.03:50 Review all three solutions.
00163 Create Weighted Random Dataset Kevin Lehrbass 2017-07-27 | http://www.myspreadsheetlab.com/video-00163-creating-a-weighted-random-data-set00:00 Introduction00:35 Steps 1,2,3,402:12 Add your own data02:28 How close is random 'state' data based on state population?Read my blog post to see enhanced solution that will allow custom variation of the random data.http://www.myspreadsheetlab.com/video-00163-creating-a-weighted-random-data-set
00162 Excel Formula Calculates Value of Chess Pieces Kevin Lehrbass 2017-06-25 | excelxor.com00:00 Challenge explanation00:28 Winning formula------detailed explanation------01:24 Step 1 Countif range "A1:H8" is CHESS BOARD01:35 Step 2 COLOR & PIECES: {"B","W"}&{"P";"N";"B";"R";"Q"} 02:05 Step 3 Create all pieces {"BP","WP";"BN","WN";"BB","WB";"BR","WR";"BQ","WQ"}03:06 Step 4 CHESS PIECE COUNT {5,5;1,1;2,1;1,1;1,1}03:42 Step 5 {-5,5;-1,1;-2,1;-1,1;-1,1} - = BLACK, +=WHITE04:12 Step 6 MULTIPLY PIECE COUNT BY PIECE VALUE {1;3;3;5;9}04:54 Step 7 {-5,5;-3,3;-6,3;-5,5;-9,9} - = BLACK, +=WHITE05:00 Step 8 Wrap it with SUM function. 05:08 Changing chess pieces updates formulaChallenge Post: excelxor.com/2014/10/15/shortest-formula-challenge-1-material-gainsSolution Post: excelxor.com/2014/10/22/shortest-formula-challenge-1-results-and-discussionMy Post: http://www.myspreadsheetlab.com/video-00162-excel-formula-calculates-value-of-chess-pieces
00161 Extract Matrix Non Blanks Into 1 Column Kevin Lehrbass 2017-06-17 | Get Excel file: http://1drv.ms/1bYwrTa00:00 Excelxor.com awesome Excel blog!!00:08 Challenge intro00:27 Excelxor solutions01:20 My solutions04:41 Make solution more efficient!!!EXCELXOR: excelxor.com/2014/08/13/single-column-from-many-containing-blanks-1-rows-firstMY POST: http://www.myspreadsheetlab.com/video-00161-extract-matrix-non-blanks-into-1-column
00160 Sum Values From Qualifying Mini Tables Kevin Lehrbass 2017-05-26 | 00:00 Requirements00:42 Normalize & Pivot Table01:41 Helper & Sumifs03:31 Array formula07:39 Review of solutionsRead my post to see additional solutions:http://www.myspreadsheetlab.com/video-00160-sum-values-from-qualifying-mini-tables-2
00159 Create unique and sorted text list Kevin Lehrbass 2017-05-07 | 0:00 Challenge Intro0:06 ASK QUESTIONS TO GET MORE DETAILS!0:41 Remove Duplicates & Sort1:16 Pivot Table 2:42 Array Formula3:19 Non Array Formula (helper columns)8:29 RecapMY POSThttp://www.myspreadsheetlab.com/video-00159-create-unique-and-sorted-text-listOSCAR CRONQUIST's POSThttp://www.get-digital-help.com/2009/05/25/create-a-drop-down-list-containing-only-unique-distinct-alphabetically-sorted-text-values-using-excel-array-formulaDAVID HAGAR's POSTdhexcel1.wordpress.com/2017/04/01/generating-a-sorted-unique-array-in-excel-using-only-formulas-by-david-hagerMIKE GIRVIN's VIDEOyoutu.be/IZZPnsRD90c
00158 Keyword Data Validation List Kevin Lehrbass 2017-03-27 | What can you do if your data validation drop down list is so long that you want to close Excel and never use it again?00:00 The Problem: super long drop down list00:34 The Idea: use keyword(s)00:46 Ask Questions: don't jump into a solution! ask questions!!!01:30 Solution 1 Pivot Table03:23 Solution 2 List Search Add-in (from Excelcampus.com)03:49 Solution 3 Array Formula (very scary!)06:12 Solution 4 Non Array Helper Formula (not scary)09:00 Solution 5 Advanced Filter
00157 Array or Aggregate to solve Excel puzzle? EMT1377 Kevin Lehrbass 2017-02-09 | Normally I would use an array but I watched Mike Girvin's EMT1377 and now I can solve this puzzle with Excel's Aggregate function!
00155 Analyze Super Sloppy Dataset Kevin Lehrbass 2017-02-09 | Do we clean it up first or build solution directly on top of this sloppy data-set?
00156 One complex formula or Helper formulas Kevin Lehrbass 2017-02-08 | This challenge is from Mike Girvin's YouTube channel ExcelIsFun.Let's assume that we can't change the structure of the data.00:00 Intro00:08 Challenge description00:56 Mike's solution01:05 Bill's solution01:19 Kevin's solution
00154 Unstack Data Challenge With Oz Kevin Lehrbass 2016-12-05 | Problem: a column of data needs to be unstacked and converted into columns and rows. Twist: the distinction where the list needs to be divided is by bold font.00:00 Challenge Intro01:05 Unstacking Logic (3 steps)01:36 Kevin's Solution08:02 Oz's SolutionGet Excel file 00154 here: http://1drv.ms/1bYwrTaCheck out Oz's channel:youtube.com/user/WalrusCandyOz's book: Guerrilla Data Analysis 2nd Editionhttp://www.amazon.com/Guerrilla-Analy...My blog: http://www.myspreadsheetlab.com/blog
00153 Oz du Soleil: Listing Unique Pairs of Students Kevin Lehrbass 2016-10-04 | Another OZ DU SOLEIL Excel challenge!Oz's video youtu.be/LBBZ5N1YlwYOz's main solution was using the incredible and life altering Excel feature GET & TRANSFORM! My Video:00:00 Explain Challenge02:42 Solution Brute Force04:19 Solution Formulas Only07:37 Solution Pivot & FormulasCheers,Kevin Lehrbasstwitter.com/kevinlehrbasshttp://www.myspreadsheetlab.com/blog
00152 MIKE GIRVIN-Unique Visitors To Site Each Day? Kevin Lehrbass 2016-09-08 | Fun challenge from Mike Girvin! I added some solutions.Download my file here: http://1drv.ms/1bYwrTa00:00 Challenge intro01:02 Mike Girvin's solutions!01:13 Pros & Cons01:51 Are there other solutions?02:03 Method1: Remove Duplicates (quick & dirty)03:00 Method2: Helper Column 1/countifs05:40 Method3: Helper Columns Match & Sumifs07:55 Method4: Array Formula 1/countifs (no helper)
00151 Custom Formats, VBA and Nerds! Kevin Lehrbass 2016-07-24 | Get Excel file 00151 here: http://1drv.ms/1bYwrTa00:00 Intro00:07 Custom Format Review01:17 Requirement01:35 Solution02:57 Solution: vba code04:14 Solution: linked pictureMy Post: http://www.myspreadsheetlab.com/video-00151-custom-formats-vba-and-nerdsCheers,Kevin Lehrbasstwitter.com/kevinlehrbasshttp://www.myspreadsheetlab.com/blog
00150 Sequential Keyword Search Dog Chases Squirrel Kevin Lehrbass 2016-06-25 | Find a sequence of words in range of messy text.Get Excel file 00150 here: http://1drv.ms/1bYwrTa00:00 Intro00:39 Requirement #1 Find feature & Countif02:24 Requirement #2 intro03:24 Requirement #2 Using Find feature05:34 Requirement #2 Dynamic requirement description06:07 Requirement #2 Dynamic Formula!09:00 Requirement #2 Dynamic hyperlink!My Post: http://www.myspreadsheetlab.com/video-00150-sequential-keyword-search-dog-chases-squirrelCheers,Kevin Lehrbasstwitter.com/kevinlehrbasshttp://www.myspreadsheetlab.com/blog
00149 Dynamically Sort Text (Oz du Soleil Challenge 001) Kevin Lehrbass 2016-06-08 | Get Excel file 00149 here: http://1drv.ms/1bYwrTaThis is a challenge from Oz du Soleil. We want to dynamically sort text excluding 'A', 'An', 'The' if found at the beginning of the title.00:00 Intro00:29 Logic (key idea)00:38 Oz's Solution01:59 My high tech solution (array)04:00 My low tech solution (manual, nor formulas!)Download Oz's sample workbook:http://datascopic.net/Kevin-Oz-TitlesOz's book: Guerrilla Data Analysis 2nd Editionhttp://www.amazon.com/Guerrilla-Analy...Oz's blog: http://datascopic.netCheers,Kevin Lehrbasstwitter.com/kevinlehrbasshttp://www.myspreadsheetlab.com/blog
00148 Insert Rows, Cascade Numbers Kevin Lehrbass 2016-04-21 | 00148 Insert Rows, Cascade NumbersGet Excel file 00148 here: http://1drv.ms/1bYwrTaWe need to insert 3 rows after each existing row. Then cascade the number below down to the right.I show you how to do this without vba or functions (using builtin Excel features like Sort, Goto Special, Fill Series).Cheers,Kevin Lehrbasstwitter.com/kevinlehrbasshttp://www.myspreadsheetlab.com/blog
00147 Conditionally Add Checkboxes using VBA Kevin Lehrbass 2016-04-01 | Get Excel file 00147 here: http://1drv.ms/1bYwrTaI created vba code to conditionally insert check boxes.I give a quick overview of how it works. You can download the file and study the VBA code found in the visual basic editor.You can definitely learn VBA if you work at it!Cheers,Kevin Lehrbasstwitter.com/kevinlehrbasshttp://www.myspreadsheetlab.com/blogfacebook.com/MySpreadsheetLab-276225542389318
00146 Step by Step Instruction Check Box Toggle Kevin Lehrbass 2016-03-29 | Get Excel file 00146 here: http://1drv.ms/1bYwrTa4 method's to show solution steps based primarily on check boxesEach method has different options.Cheers,Kevin Lehrbasstwitter.com/kevinlehrbasshttp://www.myspreadsheetlab.com/blog
00145 Wrap ROUND function around formulas Kevin Lehrbass 2016-03-20 | Get Excel file 00145 here: http://1drv.ms/1bYwrTaOnly 4 steps to wrap existing formulas with Excel's ROUND function.Cheers,Kevin Lehrbasstwitter.com/kevinlehrbasshttp://www.myspreadsheetlab.com/blog
00144 Ranked Keyword Search Kevin Lehrbass 2016-02-15 | Based on Mike Girvin's file: EMT1278-1279Mike's Files: https://people.highline.edu/mgirvin/excelisfun.htmMy Excel file 00144 EMT1278-1279: http://1drv.ms/1bYwrTaThe solution is so simple! Use Mike's formula, add a rank number beside the search words and sort descending. The preferred winner is closest to the bottom.Kevin Lehrbasstwitter.com/kevinlehrbasshttp://www.myspreadsheetlab.com/blog
00143 Retro Style Game Teaches Correlation Kevin Lehrbass 2016-02-01 | Get Excel file 00143: http://1drv.ms/1bYwrTa (00:00) Intro (00:15) Quick review of correlation (01:46) Play "GuessTheCorrelation" game (03:48) Links to learn more (04:07) Play around with data to learn! Kevin Lehrbass twitter.com/kevinlehrbass
00142 Horizontal list to Vertical list (layered transpose) Kevin Lehrbass 2016-01-16 | Get Excel file 00141 here: http://1drv.ms/1bYwrTaI first saw this challenge via a Bill Jelen (Mr Excel) tweet. The tweet directed me to Bill's free Excel help forum. Several excellent VBA solutions. In this video I explain a formula based solution.(00:00) Mr Excel forum(01:03) Requirement Description (01:40) Array formula tell us how many rows we need(01:50) Solutions steps Kevin Lehrbasstwitter.com/kevinlehrbasshttp://www.myspreadsheetlab.com/video-00142-horizontal-list-to-vertical-list-layered-transpose
00141 Formula Auditing Maestro Kevin Lehrbass 2015-12-16 | Get Excel file 00141 here: http://1drv.ms/1bYwrTa(00:00) Audit Formula: =VLOOKUP(B1,INDIRECT(VLOOKUP(A1,Data1!A6:B9,2,FALSE)),2,FALSE)(00:28) Isolate the difficult part: table_array(01:15) See the result of the inner vlookup (Sweden's range)(02:28) Result leads to Sweden's mini range and to favorite sport.---------now we audit data layout & create an easier solution--------(03:42) Complex formula due to poor data layout!(04:04) Hard coded references are dangerous! Leads to errors.(04:29) Is there a better or safer solution?(04:33) Add named ranges? Not a good idea.(06:00) SOLUTION! Normalize data, insert table, add formula.Cheers,Kevin Lehrbasstwitter.com/kevinlehrbasshttp://www.myspreadsheetlab.com/blog
Video 00140 Get Address for Largest Value Kevin Lehrbass 2015-11-20 | Get Excel file 00140 here: http://1drv.ms/1bYwrTaShow the row & column headers for the max values in a range.(00:00) Original idea from Tom Urtis(00:10) Requirement explanation(00:44) HOMEWORK: Why doesn't it work if there are ties?(01:13) Side note: Four ways to count how many max values(01:53) SOLUTION EXPLANATION (no massive multi line formula)Please add your solution as a comment below the video!!! Cheers,Kevin Lehrbasstwitter.com/kevinlehrbasshttp://www.myspreadsheetlab.com/blog
Video 00139 Beyond 3D Formulas Kevin Lehrbass 2015-10-13 | Get Excel file 00139 here: http://1drv.ms/1bYwrTaRead my blog post here: http://bit.ly/1hEISfFA review of Excel's 3D formulas and then some hefty formulas that go beyond the limitations of 3D formulas. Excel formulas can do magic but it does require careful set-up.(00:11) Data layout(01:54) Basic 3D range(03:26) Countif 3D range(06:46) Countif 3D range with improved sheet toggle(12:44) Countif 3D range with improved sheet toggle and with dynamic range! Cheers,Kevin Lehrbasstwitter.com/kevinlehrbasshttp://www.myspreadsheetlab.com/bloghttp://www.myspreadsheetlab.com/video-00139-beyond-3d-formulas
Video 00137 Vlookup Returns Multiple Values Kevin Lehrbass 2015-09-14 | Get Excel file 00137 here: http://1drv.ms/1bYwrTaSee how to make an array constant dynamic inside of a vlookup (hard to explain...easier to watch the video and see!)Cheers,Kevin Lehrbasstwitter.com/kevinlehrbasshttp://www.myspreadsheetlab.com/blog
Video 00136_MikeGirvin_EMT1230-1231Finished Kevin Lehrbass 2015-08-25 | Get Excel file 00134 here: http://1drv.ms/1bYwrTaA 4th way to solve a tedious lookup in Microsoft ExcelCheers,Kevin Lehrbasstwitter.com/kevinlehrbasshttp://www.myspreadsheetlab.com
Video 00135_Cali Data Cleanup_July 30 2015 Kevin Lehrbass 2015-07-31 | Get Excel file 00135 here: http://1drv.ms/1bYwrTaMy dog Cali has a data nightmare! Yes, this is a dramatization but this really does happen!1. (00:00) Cali's data problem description2. (01:05) Step 1 Normalize via ALT D P3. (01:27) Step 2 Extract data from pivot cache4. (01:48) Step 3 Delimit data using 'Ctrl' & 'J' not using "-"5. (02:43) Step 4 Concatenate City & Region, Alt D P6. (03:16) Step 5 Extract data from pivot cache7. (03:23) Step 6 Delete row if value is blank. Text to ColumnsCheers,Kevin Lehrbasstwitter.com/kevinlehrbass
Video 00134 Rank Idea_Jordan Goldmeier Kevin Lehrbass 2015-07-24 | Get Excel file 00134 here: http://1drv.ms/1bYwrTaSee how Jordan's rank formula uses the rare union operator and avoids a heavy array formula!Cheers,Kevin Lehrbasstwitter.com/kevinlehrbasshttp://www.myspreadsheetlab.com
Video 00133 Partial Sequential Match Kevin Lehrbass 2015-07-03 | Get Excel file 00133 here: http://1drv.ms/1bYwrTaGiven a value of '354', look for each individual digit inside of cells in a column and add up sales values to the left.I show you 2 solutions: (a) crazy array formula, (b) step by step helper column approach (much easier to explain)Idea inspired from Tom Urtis tweet (fuzzy match sum). Shorter alternative array by Zoran Stanojević. Thanks!00:00 Challenge explanation02:31 Helper column approach05:26 Crazy array formula approach!17:43 RecapCheers,Kevin Lehrbasstwitter.com/kevinlehrbasshttp://www.myspreadsheetlab.com
Video 00132 How many hours did Billy work? Kevin Lehrbass 2015-06-11 | After a long day of Excel support I came home and saw that Chandoo had given me homework.Luckily, a quick array formula can solve it !http://chandoo.org/wp/2015/06/05/how-many-hours-did-billy-work-solve-thistwitter.com/kevinlehrbasshttp://www.myspreadsheetlab.com
