SOLVED

Split column based on repeating data

Iron Contributor

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 (Iron Contributor)
Solution
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)

@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. 

@DGMalcolm 

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," ")

harshulz_0-1646965642831.png

 

 

 

This worked quite nicely, thank you!

~DGM~
1 best response

Accepted Solutions
best response confirmed by DGMalcolm (Iron Contributor)
Solution
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)

View solution in original post