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 needs to be unmerged and have its data associated with each row.  I'm trying to search on the info in Column C, but because it's merged, it won't "find" the data.  

 

I know it's hard to visualize, but I don't see where to upload the spreadsheet...

 

Thanks!

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 09, 2023

    rrachlow1990 

    if with sql,

    cli_no_header;

    select colIdxf[2:]{udf_fillna_m(%s,'a＄') %s} from consolidateSheet where f07!='Total'

     

     

     

  • 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.
      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Nov 07, 2023

        rrachlow1990 

        Attachments are available to some users only. If you don't have that option, upload the workbook to a cloud store such as Google Drive, OneDrive or Dropbox. Share the uploaded file and post a link to it in a reply.

Resources