List To Matrix - Pivot Tables FAIL but Functions Can DO It
If you have a normalized list and you want to convert it to a Matrix with column Headers and row headers, the first option that comes to your mind is to create a Pivot Table or a PIVOTBY function But Both of them will fail.
In this tutorial I convert a list (66 Records) into a matrix (132 numbers) using a simple creative solution that combines classic and dynamic array functions.
Let see the work situation
Here is the Function I explain in the Video.
=LET(A,SORT(UNIQUE(A2:A67)),
B,TRANSPOSE(A),
C,SUMIFS(C2:C67,A2:A67,A,B2:B67,B),
D,C+TRANSPOSE(C),
E,VSTACK("",A),
HSTACK(E,VSTACK(B,D)))
I am also using some conditional formatting rules to highlight the Headers and the intersection of the same city.
##############################################################
Do you want to improve your computer skills?
The OfficeInstructor center offers Can Help you achieve this goal:
Learn the skills you need at the price of a Fast-Food Meal. Register to one of the upcoming Live, instructor-led, Full Day courses (6 hours). Registration open for individuals:
a. Excel Essential Training (US$19.99) - 8 June 2024 or 13 July 2024
b. Power Query for Beginners (US$29.99) - 22 June 2024 or 27 July 2024
c. Photoshop Basic (US$34.99) - 29 June 2024
NB: 6 hours Live, Instructor-led, Ask questions, Hands-on Training, Exercise Files & Certificate of Completion
Limited Seats