Macro Help

[rrachlow1990]

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!

[#2]

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.

[#3]

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

[#4]

@rrachlow1990 The icon (and link) are just lower than you expect:

 

[#5]

@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.

[#6]

@HansVogelaar - Thank you!! I did not have the link option that @SnowMan55 pointed out, so here is the dropbox link: https://www.dropbox.com/scl/fi/2s565kudo9z8ky4a0i649/NeedsAutomation.xlsx?rlkey=tuh0p3a0cbidczfp78zs...

I suck at macros, so adding this wrinkle is just ticking me off!!

Thank you again!

[#7]

@rrachlow1990 

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

[#8]

@HansVogelaar - unfortunately, I do not.

Any thoughts, or am I just hosed?  Thanks!

[#9]

@rrachlow1990 

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

[#10 · Accepted solution]

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

[#11]

@rrachlow1990 

Also, a PowerQuery solution. Please see attached.

[#12]

@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!!!  

[#13]

Glad to help. You're welcome!

[#14]

@rrachlow1990 

if with sql,

cli_no_header;

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