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

     

     

HansVogelaar's avatar
HansVogelaar
MVP
Nov 07, 2023

rrachlow1990 

That worksheet looks like a pivot table that has been copied, then pasted as values. Don't you have the source data?

rrachlow1990's avatar
rrachlow1990
Copper Contributor
Nov 08, 2023

HansVogelaar - unfortunately, I do not.

 

Any thoughts, or am I just hosed?  Thanks!

    • rrachlow1990's avatar
      rrachlow1990
      Copper Contributor
      Nov 08, 2023

      Patrick2788 - you are brilliant!  Thank you!  It going to take me a while to work through the formula to understand it, but it's awesome!  

       

      I don't really understand Power Queries, but I would like to, so you've given me lots to work on...thanks again - great community!!!  

       

       

      • Patrick2788's avatar
        Patrick2788
        Silver Contributor
        Nov 08, 2023
        Glad to help. You're welcome!
  • 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)
)

     

     

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Nov 08, 2023

    rrachlow1990 

    Perhaps someone else knows how to do that with Power Query...

Resources