I am trying to get rid of the blank cells of the column in my sheet. I get some data from my colleagues via Google Form, and I have made some subcategory questions for them. So they get to select one of the five given choices. When I export the data to the sheet, it appears somewhat like below (linked):
https://docs.google.com/spreadsheets/d/1DBrFFNst0nhZAtxHejsrbNGkMxvcbOuie47era0Hl8c/edit?usp=sharing
Because the users can select one choice, out of five given choices four other cells must be left empty. Here's where I'm struggling with: I have used filter function to get rid of the blank cells, but then it is restricted to only set the range either single row or column. So I end up with what you would see in my sample worksheet.
I've tried arrayformula and query as well, but I couldn't figure out a way out of this.
Any help would be appreciated.
