Merge random cells in a column in sequenced order in another column

I am trying to take a list of names (stable) with designations (changing) to end up in other columns with the designation title but want them to end up in consecutive order. I have made it to where they will end up in a column but separated. I want to bring them together in another column. I will attach an example of what I am looking for help with. Thank you in advance.

In H3 array formula:

Formula:

Please Login or Register  to view this content.

and copy right/down
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.


If you want to understand better how it works start from innermost:

Formula:

Please Login or Register  to view this content.

as you copy it down it gives row numbers for non-empty cells from column C. After four rows numbers it returns errors, because there is no more data. This errors are at final stage served by outermost function - IFERROR
In the middle, there is an index function which returns data from given rows.

PS. If you do not want your helper columns - slight modification is enough and in C3 you may use array formula:

Formula:

Please Login or Register  to view this content.

and copy it right/down

If you like it, I'd appreciate marking thread solved and clicking below my post on a star (add reputation) and confirming positive reputation in a pop-up window.

Wow, $ is always important :-)

But honestly: You shall read about different modes of addressing in excel: absolute, relative and mixed. (involves both $, no $ or one $ sign)
Sample reading: https://www.excel-easy.com/functions...eferences.html