Forum Discussion

rrachlow1990's avatar
rrachlow1990
Copper Contributor
Nov 05, 2023
Solved

Macro Help

Hello all -   I've got a spreadsheet with about 3500 rows that I'm trying to clean up to be able to use in Pivot Tables.  It's got merged info in columns B & C that are causing problems.  Column B ...
Macros and VBA
  • Patrick2788's avatar
    Patrick2788
    Nov 08, 2023

    rrachlow1990 

    This formula will 'unpivot' the raw data.  I began by unmerging all cells.

     

    =LET(
    header, TAKE(matrix, 1),
    FillDown, LAMBDA(a, v, IF(v = "", a, v)),
    Faculty, INDEX(matrix, , 3),
    Course, INDEX(matrix, , 6),
    uFaculty, SORT(UNIQUE(TOCOL(Faculty, 1))),
    Semester, SCAN("", TAKE(matrix, , 1), FillDown),
    Department, SCAN("", INDEX(matrix, , 2), FillDown),
    FacultyNames, SCAN("", Faculty, FillDown),
    CrossListed, SCAN("", INDEX(matrix, , 4), FillDown),
    CourseDetails, TAKE(matrix, , -12),
    Record, HSTACK(Semester, Department, FacultyNames, CrossListed, CourseDetails),
    UnPivot, LAMBDA(a, v,
        LET(
            filtered, FILTER(Record, (FacultyNames = v) * (Course <> "Total"), ""),
            IFNA(VSTACK(a, filtered), "")
        )
    ),
    REDUCE(header, uFaculty, UnPivot)
)

     

     

rrachlow1990's avatar
rrachlow1990
Copper Contributor
Nov 08, 2023

HansVogelaar - unfortunately, I do not.

 

Any thoughts, or am I just hosed?  Thanks!

Patrick2788's avatar
Patrick2788
Silver Contributor
Nov 08, 2023

rrachlow1990 

This formula will 'unpivot' the raw data.  I began by unmerging all cells.

 

=LET(
    header, TAKE(matrix, 1),
    FillDown, LAMBDA(a, v, IF(v = "", a, v)),
    Faculty, INDEX(matrix, , 3),
    Course, INDEX(matrix, , 6),
    uFaculty, SORT(UNIQUE(TOCOL(Faculty, 1))),
    Semester, SCAN("", TAKE(matrix, , 1), FillDown),
    Department, SCAN("", INDEX(matrix, , 2), FillDown),
    FacultyNames, SCAN("", Faculty, FillDown),
    CrossListed, SCAN("", INDEX(matrix, , 4), FillDown),
    CourseDetails, TAKE(matrix, , -12),
    Record, HSTACK(Semester, Department, FacultyNames, CrossListed, CourseDetails),
    UnPivot, LAMBDA(a, v,
        LET(
            filtered, FILTER(Record, (FacultyNames = v) * (Course <> "Total"), ""),
            IFNA(VSTACK(a, filtered), "")
        )
    ),
    REDUCE(header, uFaculty, UnPivot)
)

 

 

Resources