I have some jumbled-up data I was supplied by another person on my team, as in, the type of data in the different columns does not line up row-to-row (example on 'Raw Data' tab in spreadsheet below). However, the contents of each cell do list what column they should be in, and I want to create a formula to properly organize the data to show up how it actually should, but with an arrayformula setup so it only needs to be applied to the top cell in the column. I was able to get a formula working to do so in a singular cell, but due to the amount of data I have to work with, I'd rather have an array that works across an entire column, rather than a single cell at a time.

Here's a sheet with some sample data to look at: