=char(34)&join(""",""",ARRAYFORMULA(QUERY(A1:A,"SELECT A WHERE A IS NOT NULL")&""","""&QUERY(B1:B,"SELECT B WHERE B IS NOT NULL")))&char(34)
💬 Adding Commas and Quotations for a SWITCH Formula
Aaron Davis (readwriterespond.com)To create the SWITCH, I created a UNIQUE list of all the folders and then pasted them as values so that I could delete those not required to be displayed in the directory. After culling the list, I then added the area in the cell next to each folder. Once I had these two columns complete, I used this formula to add quotation marks to them all:
=ArrayFormula(“”””&Sheet1!A:D&””””)
I then copied this list into the SWITCH formula and added in the commas
I have found a cleaner method for adding in the commas and quotations required for the SWITCH formula: