MrExcel Excel Videos

Back in episode 2297, Evan asked how to VLOOKUP between two lists where one had DOE, JANE and the other had Jane Doe. This video solves it with Power Query's new Fuzzy Match. Thanks to @XLarium @Daniel Dion and @Bart Titulaer for their suggestions that made this solution twice as quick as before.

A tough question from the MrExcel Message Board. There are 1500 rows showing products in a delimited string. Someone might have had "Cherry, Apple" yesterday and today they have "Apple, Banana, Cherry". Can you use a VBA macro to subtract Cherry Apple from Apple Banana Cherry and end up with just Banana? Today, I end up solving this with Excel Power Query, Split by Delimiter To Rows, and then a Right Anti-Join. The best part: you can refresh the query tomorrow.

We've migrated the 1.4 Million posts in the MrExcel Message Board to a new board software. One thing that is different: the steps to post your spreadsheet to the board. This video will take a look at the free XL2BB Excel add-in that you can use.

Sweeps URL: Microsoft Forms Evan has two lists of names. One is Firstname Lastname and the other is LAST, FIRST. He would like to do a VLOOKUP between these. My solution today involves running both lists through Power Query and then doing a Full Outer Join while merging the two lists.

How to do a two-way lookup during import in Excel Power Query. Sweeps URL: Microsoft Forms Recorded at Ignite 2019 in Orlando, this video shows you how to do a two-way lookup when merging files in Power Query - even if the fields are in the "wrong" order.

The MSFT Sweeps URL: Microsoft Forms Thanks to Matt Allington for this technique. Check out Matt's book: Supercharge Power BI with DAX book Today's video: How to combine data from multiple worksheets using Power Query. Combing multiple workbooks is easy, but combining Sheets is now possible, even though the method is mostly hidden. Thanks to Matt Allington, see how to solve this common problem.

A new feature announced at #MSIgnite and now available to Office Insiders: Excel can use Artificial Intelligence to Answer Questions About Your Data.

The URL in the video is Microsoft Forms While importing data into Power Query, you want to VLOOKUP to a summarized version of the data. In this video, the first step is a quick query to produce total revenue by region as a lookup query. Then, when you merge in Power Query, you will VLOOKUP to bring the Region total to each row, so you can calculate that row's percentage of region. This video debuts the Group By feature in Power Query.

URL in video: Microsoft Forms Recorded Live at Ignite 2019 in Orlando: How to import a file using Excel Power Query while doing a VLOOKUP during import.