How to align same names (with different attributes) from different columns and rows to one column

Occasional Visitor


I asked this question before with no success. So I am trying again, this time I made excel screenshot it is very easy to understand what I need, I hope everything is clear. Maybe there is some kind of formula for this, or you know a video tutorial that would help me... Thanks for any info!

Thanks a lot!



(If it doesn't load, I uploaded it to google drive)

Thanks again and have a great weekend!

1 Reply

Hello @SimonBonideco 


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