Learn to build an advanced single-cell dynamic spilled array formula that creates comprehensive customer reports by counting and listing customers across different marketing coupon groups. Master the integration of 22 Excel functions including COUNTIFS, GROUPBY, and LET to solve complex data transformation challenges that seem impossible but become manageable with modern Excel capabilities. Discover how to overcome the fundamental problem of missing customer-coupon usage records by transforming disparate data tables into meaningful reports. Explore advanced techniques for testing variables within LET functions, creating customer store records using COUNTIFS and IF functions, and understanding how Excel logical functions interpret non-zero numbers as TRUE. Dive deep into GROUPBY functionality for counting customers in groups, utilizing COUNTA, ARRAYTOTEXT, HSTACK, and DROP functions for multi-column sorting and multiple calculations. Master the art of splitting customer names from arrays using TEXTJOIN and TEXTSPLIT functions to solve the dynamic spilled array challenge of displaying arrays within arrays. Understand the power of REDUCE and LAMBDA functions for advanced array manipulation, and learn to combine TRANSPOSE and HSTACK for final report generation. Gain expertise in functions including TOROW, SORT, UNIQUE, COLUMNS, EXPAND, SEQUENCE, ROWS, VSTACK, MAX, and TRANSPOSE while discovering how to make arrays spill arrays of arrays effectively.

Syllabus

1. 00:00 Introduction.
2. 00:24 Methods we used back in 2018 to solve this complex reporting task.
3. 00:37 Goal of report: List unique coupon groups with the count and list of customers in each group.
4. 01:27 Fundamental problem with data: We have a customer list and a coupon sales table, but no set of records that list which coupons each customer used. We will have to transform the data into a table of customer coupon usage records.
5. 02:01 Look at tables of data given and final report.
6. 02:40 Formula #1
7. 02:40 LET functions to define variables. Used when formula elements are repeated in complex formula.
8. 03:27 TOROW, UNIQUE and SORT functions to create Store variable.
9. 04:12 How to test the variables that you create in the LET functions.
10. 05:05 Look at the set of records that we must create to solve this problem.
11. 05:18 COLUMNS function to create Count Stores variable.
12. 05:43 COUNTIFS and IF function to create Customer Store Records variable.
13. 07:16 All Excel Logical Functions IF, IFS, AND, OR, FILTER and many more interpret any non-zero number as TRUE and zero as FALSE.
14. 08:17 GROUPBY, COUNTA, ARRAYTOTEXT, HSTACK and DROP functions to create Groupby variable that will count the customers in each group.
15. 10:05 How to sort when there are more than one column in the row_fields argument of GROUPBY function by using the field_relationship argument.
16. 11:05 HSTACK function to make multiple calculations in a GROUPBY function report.
17. 12:21 DROP function to drop the annoying default first row labels in a multiple calculation GROUPBY reports.
18. 12:49 SEQUENCE and ROWS functions to create Report First Column variable.
19. 13:42 DROP and EXPAND functions to create Report Middle Column variable.
20. 14:59 TAKE function to create Report Last Column variable.
21. 15:33 TEXTJOIN & TEXTSPLIT functions to split customer names from an 8 row array into an array for each row. This step solves the dynamic spilled array formula problem of spilling an array of arrays.
22. 18:41 TRANSPOSE & HSTACK to create final report!
23. 19:31 REDUCE, LAMBDA, VSTACK, EXPAND, TEXTSPLIT, MAX and DROP functions to split customer names from an 8 row array into an array for each row. This step solves the dynamic spilled array formula problem of spilling an array of arrays.
24. 20:04 What is REDUCE function and what is LAMBDA function?
25. 24:19 Summary
26. 24:31 Closing