excelvbaexcel-formulaoffice365

Code to place rows next to each other in Excel

I'm looking for a short code (or formula?) that I could use to rearrange my data, particularly to place rows next to each other (see picture).

Rearrange rows next to each other

Thank you.

The closest I found was a formula that combines the use of MOD and OFFSET (https://www.youtube.com/watch?v=Tt4nUYVp2v0&ab_channel=MikeWong), but this does not quite do what I want.

Data:

A B C
1
2 1 3112.5 947
3 2 3087.5 937.5
4 3 1271 1162
5 4 1086 1239
6 1 3112.5 947
7 2 3087.5 937.5
8 3 1271 1162
9 4 1086 1239
10 1 3112.5 947
11 2 3087.5 937.5
12 3 1271 1162
13 4 1086 1239
14 1 3112.5 947
15 2 3087.5 937.5
16 3 1271 1162
17 4 1086 1239
18 1 3112.5 947
19 2 3087.5 937.5
20 3 1271 1162
21 4 1086 1239
Solution

  • Since you tagged Excel 2010 it was a bit challenging getting the results as shown (paste formula and drag to right/down):

    =IFERROR(
         IF(COLUMN(A1)-1 / ((COLUMN(A1)-1)/2<=MAX($A$2:$A$21)) / (ROW(A1)<=(ROWS($A$2:$A$21)/MAX($A$2:$A$21))),
            INDEX($B$2:$C$21,
                  1+(ROW(A1)-1)*MAX($A$2:$A$21)+(COLUMN(A1)-2)/2,
                  1+ISODD(COLUMN(A1))),
            ROW(A1)),
         "")

    enter image description here

    With Office 365 this all can be a lot easier: =LET(w,WRAPROWS(TOCOL(B2:C21),8),HSTACK(SEQUENCE(ROWS(w)),w))

    or dynamic:

    =LET(range,A2:C21,
     pattern,TAKE(range,,1),
     values,DROP(range,,1),
     n,MAX(pattern),
     wrap,WRAPROWS(TOCOL(values),n*COLUMNS(values)),
HSTACK(SEQUENCE(ROWS(wrap)),wrap))

    On Beta you could also use PIVOTBY: =PIVOTBY(1+INT((ROW(B2:C21)-ROW(B2))/MAX(A2:A21)),A2:A21,B2:C21,SUM,,0,,0)

    enter image description here