HTML:
link to excel: https://1drv.ms/x/s!AoMCBhqmW_jtkzg4v5o7-j9hB0MA (read-only)
Hi, I'm hoping someone can help me on this.
Result table shows extracted distinct names, sorted based on two criteria: firstly whether or not the person is last known as active (or non-active) and secondly the corresponding month with most recent month shown first (descending order).
Primary goal is using a single helper column I to extract distinct sorted names, into column L. As you can see, the formula looks mind boggling (to me at least) and is ridden with (at least) one known duplicate. Problem is I'm foreseeing a potential thousands of rows of raw data. Question is, is there any way to simplify the helper column formula or even overhaul it, without needing to use array formula? As for pivottable, I have zero knowledge and I reckon I probably need to spend a good amount of time to learn it from scratch. For info. helper columns E to H are only temporary to show working sequence.
Secondary goal 1; currently, result table shows that names are listed continuously from Active and then non-active. Is there a way to build into the current formula (column L) such that a blank row is generated to separate Active and non-active rows?
Secondary goal 2; I'm still working on automating column K and M...slowly. So, if someone with better brain processing power (as compared to me
) can help to figure it out, then I'd be more than happy. (thumbs up)Thanks folks!
