Hi all, hoping someone may be able to point me in the right direction with this one.
I'm running a dataset of approx. 40k individuals, and tracking progress of up to 100 unique activities, combining all entries into a single concatenated output for each individual. Currently this works through formulas across multiple sheets (thousands of IF/XLOOKUP statements and the longest concatenate formula in existence) - it works but the file size is massive, and most importantly it doesn't play well when the number of individuals/activities increases.
Ideally I'm looking to run this entire process through Power Query, whereby changes in individual and activity numbers is not a limiting factor. The source data is itself generated by a different Power Query, so it's nice and easy to reference.
I'm aiming for the output cell for each individual to detail the full summary of activities conducted (including the date) on a unique line, with activities not completed by that individual not included at all. Individuals completing no activities simply return a blank field.
Importantly, the name/volume/order of activities and individuals will change constantly; I've made some headway, but I'm struggling to account for these changes.
Any help would be really appreciated!
Bookmarks