SOLVED

Contributor

# 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 first, last, phone, repeatedly. I want to end up with those 3 columns. Can't figure out how to convert this data. Thoughts?

TIA

~DGM~

 Darrell Majors 123-456-7890 Jane Davis 234-567-8901 Alex Joukis 345-678-9012
4 Replies
best response confirmed by DGMalcolm (Contributor)
Solution

# Re: Split column based on repeating data

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)

# Re: Split column based on repeating data

``=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.

# Re: Split column based on repeating data

there is a tricky way, slight thousands times better in my overview
and this will help of macros will get perfect result

=FILTER(\$B2:\$B1000,\$A2:\$A1000=E2," ")

# Re: Split column based on repeating data

This worked quite nicely, thank you!

~DGM~