stack columns into multiple columns

[Urraco]

Hi everybody!

I have a huge list, over 600.000 rows
I would like to distribute the columns x1 and x2 in groups of 500 rows in order, exactly as in the example below ( in the example I put only 5 rows to simplify)
is it possible with a power query or formula?
Thank yery much!

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	x1	x2	a1	a2	b1	b2	c1	c2	d1	d2	e1	e2	f1	f2	g1	g2	h1	h2	i1	i2
2	1	1982	1	1982	6	1987	11	1992	16	1983	21	1997	26	2002	31	2007	36	2012	41	2017
3	2	1983	2	1983	7	1988	12	1993	17	1982	22	1998	27	2003	32	2008	37	2013	42	2018
4	3	1984	3	1984	8	1989	13	1994	18	1983	23	1999	28	2004	33	2009	38	2014	43	2019
5	4	1985	4	1985	9	1990	14	1995	19	1982	24	2000	29	2005	34	2010	39	2015	44	2020
6	5	1986	5	1986	10	1991	15	1996	20	1983	25	2001	30	2006	35	2011	40	2016	45	2021
7	6	1987	46	2022	51	2027	56	2032	61	2037	66	2042	71	2047	76	2052	81	2057	86	2062
8	7	1988	47	2023	52	2028	57	2033	62	2038	67	2043	72	2048	77	2053	82	2058	87	2063
9	8	1989	48	2024	53	2029	58	2034	63	2039	68	2044	73	2049	78	2054	83	2059	88	2064
10	9	1990	49	2025	54	2030	59	2035	64	2040	69	2045	74	2050	79	2055	84	2060	89	2065
11	10	1991	50	2026	55	2031	60	2036	65	2041	70	2046	75	2051	80	2056	85	2061	90	2066
12	11	1992	91	2067	96	2072
13	12	1993	92	2068
14	13	1994	93	2069
15	14	1995	94	2070
16	15	1996	95	2071
17	16	1997
18	17	1998
19	18	1999
20	19	2000
21	20	2001
22	21	2002
23	22	2003
24	23	2004
25	24	2005
26	25	2006
27	26	2007
28	27	2008
29	28	2009
30	29	2010
31	30	2011
32	31	2012
33	32	2013
34	33	2014
35	34	2015
36	35	2016
37	36	2017
38	37	2018
39	38	2019
40	39	2020
41	40	2021
42	41	2022
43	42	2023
44	43	2024
45	44	2025
46	45	2026
47	46	2027
48	47	2028
49	48	2029
50	49	2030
51	50	2031
52	51	2032
53	52	2033
54	53	2034
55	54	2035
56	55	2036
57	56	2037
58	57	2038
59	58	2039
60	59	2040
61	60	2041
62	61	2042
63	62	2043
64	63	2044
65	64	2045
66	65	2046
67	66	2047
68	67	2048
69	68	2049
70	69	2050
71	70	2051
72	71	2052
73	72	2053
74	73	2054
75	74	2055
76	75	2056
77	76	2057
78	77	2058
79	78	2059
80	79	2060
81	80	2061
82	81	2062
83	82	2063
84	83	2064
85	84	2065
86	85	2066
87	86	2067
88	87	2068
89	88	2069
90	89	2070
91	90	2071
92	91	2072
93	92	2073
94	93	2074
95	94	2075
96	95	2076
97	96	2077
98	97	2078
99	98	2079
100	99	2080
101	100	2081
102	101	2082
103	102	2083
104	103	2084
105	104	2085
106	105	2086
107	106	2087
108	107	2088
109	108	2089
110	109	2090
111	110	2091
112	111	2092
113	112	2093
114	113	2094
115	114	2095
116	115	2096
117	116	2097
118	117	2098
119	118	2099
120	119	2100
121	120	2101
Sheet1

 

[awoohaw]

how many groups do you want to go over (in both scenarios (your 500 and the 50 in the example? I ask this because the above is not groups of 50, it is groups of 45?
It just seems inconsistent if you were doing a proportional scenario in the mini workbook example.

 

[Georgiboy]

Perhaps the below will get you started; sorry the variables are not named well - i have been short of time:

Sub test()
    Dim rng As Range, var As Variant
    Dim blocks As Long, rws As Long
    Dim StartCol As Long
    Dim a As Long, b As Long, c As Long, d As Long
    
    Set rng = Range("A2:B" & Range("A" & Rows.Count).End(xlUp).Row)
    var = rng.Value
    a = 1
    
    StartCol = 3 ' start column of results
    blocks = 9 ' how many blocks of 2 columns to use
    rws = 5 ' how many rows of data to include in each block
    
    Do
        For b = StartCol To (blocks * 2) + 1 Step 2
            For c = 2 + d To (rws + 1) + d
                If a > rng.Rows.Count Then Exit Do
                Cells(c, b) = var(a, 1)
                Cells(c, b + 1) = var(a, 2)
                a = a + 1
            Next c
        Next b
        d = d + (rws + 1)
    Loop
End Sub

The above could proably be streamlined also

It does not add headers to the blocks - not sure if the letters were essential or not?

 

[awoohaw]

Here is a formula solution:

mr excel questions 13.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	x1	x2	a1	a2	b1	b2	c1	c2	d1	d2	e1	e2	f1	f2	g1	g2	h1	h2	i1	i2
2	1	1982	1	1982	6	1987	11	1992	16	1997	21	2002	26	2007	31	2012	36	2017	41	2022
3	2	1983	2	1983	7	1988	12	1993	17	1998	22	2003	27	2008	32	2013	37	2018	42	2023
4	3	1984	3	1984	8	1989	13	1994	18	1999	23	2004	28	2009	33	2014	38	2019	43	2024
5	4	1985	4	1985	9	1990	14	1995	19	2000	24	2005	29	2010	34	2015	39	2020	44	2025
6	5	1986	5	1986	10	1991	15	1996	20	2001	25	2006	30	2011	35	2016	40	2021	45	2026
7	6	1987	46	2027	51	2032	56	2037	61	2042	66	2047	71	2052	76	2057	81	2062	86	2067
8	7	1988	47	2028	52	2033	57	2038	62	2043	67	2048	72	2053	77	2058	82	2063	87	2068
9	8	1989	48	2029	53	2034	58	2039	63	2044	68	2049	73	2054	78	2059	83	2064	88	2069
10	9	1990	49	2030	54	2035	59	2040	64	2045	69	2050	74	2055	79	2060	84	2065	89	2070
11	10	1991	50	2031	55	2036	60	2041	65	2046	70	2051	75	2056	80	2061	85	2066	90	2071
12	11	1992	91	2072	96	2077	101	2082	106	2087	111	2092	116	2097	0	0	0	0	0	0
13	12	1993	92	2073	97	2078	102	2083	107	2088	112	2093	117	2098	0	0	0	0	0	0
14	13	1994	93	2074	98	2079	103	2084	108	2089	113	2094	118	2099	0	0	0	0	0	0
15	14	1995	94	2075	99	2080	104	2085	109	2090	114	2095	119	2100	0	0	0	0	0	0
16	15	1996	95	2076	100	2081	105	2086	110	2091	115	2096	120	2101	0	0	0	0	0	0
17	16	1997	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
18	17	1998	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
19	18	1999	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
20	19	2000	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
Sheet11 (2)

Cell Formulas
Range	Formula
C2:T20	C2	=INDEX( IF(ISODD(COLUMN(C2)), 1*($A$2:$A$600000),1*($B$2:$B$600000)), MOD((ROW(C2)-2),5)+1+ (INT((COLUMN(C2)-3)/2))*5+ INT((ROW(C2)-2)/5)*45,1)

 

[awoohaw]

in this formula (from above), you will need to change the 45 to 500 (or whatever number you want in each section)
the first entry of the formula must be in cell C2, or you need to adjust it.

=INDEX( IF(ISODD(COLUMN(C2)), 1*($A$2:$A$600000),1*($B$2:$B$600000)), MOD((ROW(C2)-2),5)+1+ (INT((COLUMN(C2)-3)/2))*5+ INT((ROW(C2)-2)/5)*45,1)

 

[Urraco]

Tahks to all !!!

This macro almost does the job, the problem is that just adds an empty rows
can be solved?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	a1	a2	x1	x2	x3	x4	x5	x6	x7	x8	x9	x10	x11	x12	x13	x14	x15	x16	x17	x18
2	1	1982	1	1982	6	1987	11	1992	16	1997	21	2002	26	2007	31	2012	36	2017	41	2022
3	2	1983	2	1983	7	1988	12	1993	17	1998	22	2003	27	2008	32	2013	37	2018	42	2023
4	3	1984	3	1984	8	1989	13	1994	18	1999	23	2004	28	2009	33	2014	38	2019	43	2024
5	4	1985	4	1985	9	1990	14	1995	19	2000	24	2005	29	2010	34	2015	39	2020	44	2025
6	5	1986	5	1986	10	1991	15	1996	20	2001	25	2006	30	2011	35	2016	40	2021	45	2026
7	6	1987
8	7	1988	46	2027	51	2032	56	2037	61	2042	66	2047	71	2052	76	2057	81	2062	86	2067
9	8	1989	47	2028	52	2033	57	2038	62	2043	67	2048	72	2053	77	2058	82	2063	87	2068
10	9	1990	48	2029	53	2034	58	2039	63	2044	68	2049	73	2054	78	2059	83	2064	88	2069
11	10	1991	49	2030	54	2035	59	2040	64	2045	69	2050	74	2055	79	2060	84	2065	89	2070
12	11	1992	50	2031	55	2036	60	2041	65	2046	70	2051	75	2056	80	2061	85	2066	90	2071
13	12	1993
14	13	1994	91	2072	96	2077	101	2082	106	2087	111	2092	116	2097	121	2102	126	2107	131	2112
15	14	1995	92	2073	97	2078	102	2083	107	2088	112	2093	117	2098	122	2103	127	2108	132	2113
16	15	1996	93	2074	98	2079	103	2084	108	2089	113	2094	118	2099	123	2104	128	2109	133	2114
17	16	1997	94	2075	99	2080	104	2085	109	2090	114	2095	119	2100	124	2105	129	2110	134	2115
18	17	1998	95	2076	100	2081	105	2086	110	2091	115	2096	120	2101	125	2106	130	2111	135	2116
19	18	1999
20	19	2000	136	2117	141	2122	146	2127	151	2132	156	2137	161	2142	166	2147	171	2152	176	2157
21	20	2001	137	2118	142	2123	147	2128	152	2133	157	2138	162	2143	167	2148	172	2153	177	2158
Sheet1

Perhaps the below will get you started; sorry the variables are not named well - i have been short of time:

Sub test()
    Dim rng As Range, var As Variant
    Dim blocks As Long, rws As Long
    Dim StartCol As Long
    Dim a As Long, b As Long, c As Long, d As Long
  
    Set rng = Range("A2:B" & Range("A" & Rows.Count).End(xlUp).Row)
    var = rng.Value
    a = 1
  
    StartCol = 3 ' start column of results
    blocks = 9 ' how many blocks of 2 columns to use
    rws = 5 ' how many rows of data to include in each block
  
    Do
        For b = StartCol To (blocks * 2) + 1 Step 2
            For c = 2 + d To (rws + 1) + d
                If a > rng.Rows.Count Then Exit Do
                Cells(c, b) = var(a, 1)
                Cells(c, b + 1) = var(a, 2)
                a = a + 1
            Next c
        Next b
        d = d + (rws + 1)
    Loop
End Sub

The above could proably be streamlined also

It does not add headers to the blocks - not sure if the letters were essential or not?

 

[awoohaw]

@Urraco , did you consider the formula in post 4?

 

[Georgiboy]

I left the blank rows in case you wanted headers, the below is with no blank rows:

Sub test()
    Dim rng As Range, var As Variant
    Dim blocks As Long, rws As Long
    Dim StartCol As Long
    Dim a As Long, b As Long, c As Long, d As Long
    
    Set rng = Range("A2:B" & Range("A" & Rows.Count).End(xlUp).Row)
    var = rng.Value
    a = 1
    
    StartCol = 3 ' start column of results
    blocks = 9 ' how many blocks of 2 columns to use
    rws = 5 ' how many rows of data to include in each block
    
    Do
        For b = StartCol To (blocks * 2) + 1 Step 2
            For c = 1 + d To rws + d
                If a > rng.Rows.Count Then Exit Do
                Cells(c, b) = var(a, 1)
                Cells(c, b + 1) = var(a, 2)
                a = a + 1
            Next c
        Next b
        d = d + rws
    Loop
End Sub

 

[Urraco]

Thank you!
I get this error

Book3
	A	B	C	D	E	F	G
2	1	1982	1	1982	#REF!	#REF!	#REF!
3	2	1983
4	3	1984
5	4	1985
6	5	1986
7	6	1987
8	7	1988
Sheet1

Cell Formulas
Range	Formula
C2:G2	C2	=INDEX( IF(ISODD(COLUMN(C2)), 1*($A$2:$A$600000),1*($B$2:$B$600000)), MOD((ROW(C2)-2),5)+1+ (INT((COLUMN(C2)-3)/2))*5+ INT((ROW(C2)-2)/5)*45,1)

@Urraco , did you consider the formula in post 4?

 

[awoohaw]

@Urraco , I do not know why you would get that. I just copy and pasted your mini worksheet into a new workbook and I got this:

mr excel questions 13.xlsm
	A	B	C	D	E	F	G
1
2	1	1982	1	1982	6	1987	0
3	2	1983
4	3	1984
5	4	1985
6	5	1986
7	6	1987
8	7	1988
Sheet12

Cell Formulas
Range	Formula
C2:G2	C2	=INDEX( IF(ISODD(COLUMN(C2)), 1*($A$2:$A$600000),1*($B$2:$B$600000)), MOD((ROW(C2)-2),5)+1+ (INT((COLUMN(C2)-3)/2))*5+ INT((ROW(C2)-2)/5)*45,1)