Forum Discussion

DGMalcolm's avatar
DGMalcolm
Iron Contributor
Mar 10, 2022
Solved

Split column based on repeating data

Hey there,   I've got a data set that has all of the info in the first column and I want to split it into columns based on every x number of rows. Like the data at the bottom where the rows contain...
excel
  • Patrick2788's avatar
    Patrick2788
    Mar 10, 2022
    Presuming your names are in A with no header and you're on 365, this is the idea:

    =INDEX(FILTER(A:A,A:A<>""),SEQUENCE(COUNTA(A:A)/3,3,1,1),1)
OliverScheurich's avatar
OliverScheurich
Gold Contributor
Mar 10, 2022

DGMalcolm 

=IF(AND(COLUMN()=2,MOD(ROW(),3)=1),$A1,IF(AND(COLUMN()=3,MOD(ROW(),3)=2),$A1,IF(AND(COLUMN()=4,MOD(ROW(),3)=0),$A1,"")))

 Maybe with this formula which i entered in cell B1 and copied across range B1:D9 in the attached example.

 

You can then copy range B1:D9 and then paste only values. Then select empty cells and delete empty cells. 

Resources