In column A I have data that I would like to have transposed to individual columns. Each column should have a predetermined number of columns between each data set. If I would like ot change it from 1 column to 5 columns (or some other number) I would like to be able to do so. Is it achievable through either a VBA script or a formula?
Example:
I found a formula that works well in Google Sheets however it doesn't work very well if I try to convert it to Excel.
MYDATA = named range
Example:
| A | B | C | D | E | F | G | H | I | J |
| Data1 | Data1 | Data2 | Data3 | Data4 | Data5 | ||||
| Data2 | |||||||||
| Data3 | |||||||||
| Data4 | |||||||||
| Data5 | |||||||||
| Data6 | |||||||||
| Data7 | |||||||||
| Data8 |
I found a formula that works well in Google Sheets however it doesn't work very well if I try to convert it to Excel.
Excel Formula:
=ArrayFormula(IFERROR(VLOOKUP(SEQUENCE(1,COUNTA(MYDATA)*8,8,1)/8,{SEQUENCE(COUNTA(MYDATA),1,1,1),FILTER(MYDATA,LEN(MYDATA))},2,0),))MYDATA = named range
