To help (others) to help you I would recommend you update your initial post with:
- Exact version of Excel on Mac
- Does this version offers functions like FILTER, SORT, LAMBDA...
- Clarify: Do you always have the same number of 1A, 2A, 3A... in your matrix?
I can get what you expect with Power Query. However this requires a few transformation steps and as far as I know, even with the most recent (non-Beta) version of Excel on Mac, you wouldn't be able to refresh that query but I'm not 100% sure - If you want to try let me know and I'll share it