Formula simplification for Extracting distinct sorted values based on criteria using helper column

Dinghy

New Member
Joined
Mar 13, 2018
Messages
2
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 :p) can help to figure it out, then I'd be more than happy. (thumbs up)

Thanks folks!
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Dinghy

New Member
Joined
Mar 13, 2018
Messages
2
Forgot to mention that no VBA as well please. I couldn't edit the original post since I don't have permission (yet?). Thanks.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,176
Messages
5,600,147
Members
414,365
Latest member
UUR

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top