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

4 Replies

  • harshulz's avatar
    harshulz
    Iron Contributor
    Mar 11, 2022

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

     

     

     

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

  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    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)

Resources