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)
)

     

     

peiyezhu's avatar
peiyezhu
Bronze Contributor
Nov 06, 2023
know it's hard to visualize, but I don't see where to upload the spreadsheet.


you can click
Open full text editor

then click attachment icon to upload files.
rrachlow1990's avatar
rrachlow1990
Copper Contributor
Nov 07, 2023

peiyezhu  - I don't mean to be obtuse here, but I just don't see the "attachment" icon 😞

Resources