Completed
10. 04:12 IFS, SEQUENCE, COLUMNS and TOCOL functions to deliver column positions from cross tabulated table column headers column criteria
Class Central Classrooms beta
YouTube videos curated by Class Central.
Classroom Contents
Excel Formula to UnPivot: Convert Cross-Tab Table to Proper Table Using LAMBDA - EMT 1887
Automatically move to the next video in the Classroom when playback concludes
- 1 1. 00:00 Introduction
- 2 2. 00:03 What we did in EMT 1886: Power Query UnPivot
- 3 3. 00:17 ExcelLambda left comment about UnPivot Formula
- 4 4. 00:33 Why we might want a formula to UnPivot
- 5 5. 01:05 Three Part Solution: Build Formulas, LET function to remove repetition, LAMBDA function to create re-usable function
- 6 6. 01:22 Build formula to unpivot a cross tabulated table, piece by piece
- 7 7. 01:22 IFS Function formula element to deliver an #N/A error when data is missing in cross tab table.
- 8 8. 02:15 SEQUENCE & ROWS functions used with IFS to deliver row positions from cross tabulated table row headers row criteria
- 9 9. 02:59 TOCOL function to skip #N/A errors when data is missing in cross tab table and you want to skip a record in the resulting table
- 10 10. 04:12 IFS, SEQUENCE, COLUMNS and TOCOL functions to deliver column positions from cross tabulated table column headers column criteria
- 11 11. 05:05 CHOOSEROWS function to generate a column, a proper field, in the resulting unpivoted table for the row headers row criteria in the cross tabulated table.
- 12 12. 05:52 CHOOSEROWS & TRANSPOSE functions to generate a column, a proper field, in the resulting unpivoted table for the column headers column criteria in the cross tabulated table.
- 13 13. 06:31 TOCOL, IF & IFS Functions to generate a column, a proper field, in the resulting unpivoted table for the values on the inside of the cross tabulated table.
- 14 14. 07:09 Mash three main formula elements into a single cell formula that uses the HSTACK function. Learn how to use the Clipboard and Ctrl + C, C keyboard shortcut to mash together formulas element…
- 15 15. 08:10 LET Function to remove repeating formulas elements and make the formula more efficient. Learn that the LET Function can define variables in a worksheet formula.
- 16 16. 08:56 Why we use the LET function
- 17 17. 09:42 Testing variables in the LET function
- 18 18. 12:18 LAMBDA function to define a re-usable worksheet function. We use LAMBDA to define a new UnPivot function
- 19 19. 12:30 Create arguments in the new LAMBDA defined function
- 20 20. 14:07 Test the new LAMBDA function
- 21 21. 15:40 Paste LAMBDA function into the Defined Name Dialog Box
- 22 22. 16:22 Use the new LAMBDA function: UnPivot
- 23 23. 16:43 Bonus: Add field name argument to the MABDA function
- 24 24. 17:27 Summary
- 25 25. 17:35 Closing
