MrExcel Excel Videos

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.

Here is a look at the final version of XLOOKUP, along with 10 benefits of XLOOKUP over VLOOKUP, HLOOKUP, LOOKUP, INDEX/MATCH. Table of Contents (0:00 Welcome and Introduction to Excel XLOOKUP Deep Dive (0:25) List of Benefits (2:30) First XLOOKUP (3:02) 3rd argument is not an integer (3:29) Handle items not found (3:51) Range lookup find value just less like VLOOKUP(,,,True) (4:41) Find value just larger like MATCH - without sorting (5:16) HLOOKUP replaced by XLOOKUP (5:30) Search from bottom of table (6:21) Wildcards in XLOOKUP versus VLOOKUP (6:56) Return all 12 months from table with single formula (7:38) XLOOKUP can return a cell reference XLOOKUP:XLOOKUP (9:10) Two-Way Match (10:09) Sum All lookups (10:44) Twisted XLOOKUP (11:13)...