I have a table structured as shown in the image. I utilized the formula =filter('Form Responses 1'!A:BJ,'Form Responses 1'!F:F=subjects!A19) to filter data. What I need now is to disregard any empty cells and shift the last three columns into specific positions. Specifically, I want columns N, O, and P to occupy columns H, I, and J. Additionally, I require the other columns to the right of column G to be moved into the corresponding positions starting from H, I, and J. Any assistance would be greatly appreciated!
You can achieve this by creating a new array with filtered results and rearranged columns. First, use the FILTER function to isolate the rows you want. Then, you can create a new array by manually setting column order. For moving columns N, O, and P to H, I, J in the result, use an ARRAYFORMULA with CHOOSE or nested INDEX to reference correct column placements. It might be a little complex for large datasets, but using ARRAYFORMULA can streamline the process effectively.