Nov 05 2023 02:18 PM
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!
Nov 06 2023 05:44 AM
Nov 06 2023 05:27 PM
@peiyezhu - I don't mean to be obtuse here, but I just don't see the "attachment" icon :(
Nov 06 2023 09:31 PM
Nov 07 2023 04:43 AM
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.
Nov 07 2023 10:33 AM
@Hans Vogelaar - 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!
Nov 07 2023 03:13 PM
That worksheet looks like a pivot table that has been copied, then pasted as values. Don't you have the source data?
Nov 07 2023 04:09 PM
Nov 08 2023 03:05 AM
Perhaps someone else knows how to do that with Power Query...
Nov 08 2023 06:59 AM - edited Nov 08 2023 07:15 AM
SolutionThis 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)
)
Nov 08 2023 07:14 AM
Also, a PowerQuery solution. Please see attached.
Nov 08 2023 07:39 AM
@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!!!
Nov 08 2023 05:11 PM - edited Nov 08 2023 05:11 PM
if with sql,
cli_no_header;
select colIdxf[2:]{udf_fillna_m(%s,'a$') %s} from consolidateSheet where f07!='Total'