Sorting and moving data

%3CLINGO-SUB%20id%3D%22lingo-sub-2320077%22%20slang%3D%22en-US%22%3ESorting%20and%20moving%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2320077%22%20slang%3D%22en-US%22%3E%3CP%3EIn%20column%20A%2C%20I%20have%20pasted%20data%20from%20a%20web%20site.%26nbsp%3B%20The%20data%20in%20A1%20is%20name%20--%20A2%20is%20street%20address%20--%20A3%20is%20city%2C%20state%20zip%20--%20A4%20is%20email%20address%26nbsp%3B%20--%20A5%20is%20telephone%20number.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20data%20repeats%20A6%20is%20name%20--%20A7%20is%20street%20address%20--%20etc.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20like%20to%20cut%20A1%2C%20A6%2C%20A11%2C%20A16%2C%20A21%2C%20A26%2C%20etc%20and%20paste%20the%20cells%20into%20column%20B%20(Name).%3C%2FP%3E%3CP%3EI%20would%20like%20to%20cut%20A2%2C%20A7%2C%20A12%2C%20A17%2C%20A22%2C%20A27%2C%20etc%20and%20paste%20the%20cells%20into%20column%20C%20(Street%20Address).%3C%2FP%3E%3CP%3ERepeat%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20a%20very%20small%20amount%20of%20data%20I%20could%20do%20this%20manually%20and%20do%20it%20quickly.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20large%20database.%26nbsp%3B%20Is%20there%20a%20function%20(or%20similar)%20that%20I%20can%20utilize%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2320077%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2320166%22%20slang%3D%22en-US%22%3ERe%3A%20Sorting%20and%20moving%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2320166%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1044241%22%20target%3D%22_blank%22%3E%40Ken_Tanner%3C%2FA%3E%26nbsp%3BIn%20B1%2C%20you%20could%20use%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DINDEX(%24A%3A%24A%2C(ROW()-ROW(%24A%241))*5%2BCOLUMN()-COLUMN(%24A%241))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3BCopy%20to%20the%20right%20and%20down.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202021-05-04%20at%2006.22.10.png%22%20style%3D%22width%3A%20442px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F277741iEA0860E8645C6D84%2Fimage-dimensions%2F442x243%3Fv%3Dv2%22%20width%3D%22442%22%20height%3D%22243%22%20role%3D%22button%22%20title%3D%22Screenshot%202021-05-04%20at%2006.22.10.png%22%20alt%3D%22Screenshot%202021-05-04%20at%2006.22.10.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EExample%20attached.%3C%2FP%3E%3CP%3EHowever%2C%20if%20this%20is%20a%20recurring%20process%20and%20since%20you%20mentioned%20that%20your%20list%20has%20thousands%20of%20row%2C%20perhaps%20you%20would%20consider%20using%20Power%20Query.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

In column A, I have pasted data from a web site.  The data in A1 is name -- A2 is street address -- A3 is city, state zip -- A4 is email address  -- A5 is telephone number.

 

This data repeats A6 is name -- A7 is street address -- etc.  

 

I would like to cut A1, A6, A11, A16, A21, A26, etc and paste the cells into column B (Name).

I would like to cut A2, A7, A12, A17, A22, A27, etc and paste the cells into column C (Street Address).

Repeat

 

For a very small amount of data I could do this manually and do it quickly.

 

I have a large database.  Is there a function (or similar) that I can utilize?

 

Thanks!

 

2 Replies

@Ken_Tanner In B1, you could use:

 

=INDEX($A:$A,(ROW()-ROW($A$1))*5+COLUMN()-COLUMN($A$1))

 

 Copy to the right and down.

Screenshot 2021-05-04 at 06.22.10.png

Example attached.

However, if this is a recurring process and since you mentioned that your list has thousands of rows, perhaps you would consider using Power Query.

@Ken_Tanner 

As variant

=LET(range, A2:INDEX(A:A, COUNTA(A:A)),
     n, 5,
     INDEX(range,SEQUENCE(ROWS(range)/n,n)))