Copy a list of Numbers based on row header (matrix table)

[KasperSSI]

Hi,

Is there a formula or VBA solution for this following issue:

In coloumn A2:A30 i have a list of Account Numbers and In B1:M1 i have the months.


I want to make an import sheet - but this requires that the format is changed to
A2:A30 = accounts and B2:B30 = January
A31:A61 = same accounts and B31:B61 = February. And so forth until there is no more headers In row1.

I am thinking VBA might be the soloution - but dont know how to go about it.

Any sugestions?

 

[hagia_sofia]

Something along these lines?

=HSTACK(TOCOL(IF(SEQUENCE(,12),A2:A30),,TRUE),TOCOL(IF(SEQUENCE(ROWS(A2:A30)),B1:M1),,TRUE))

 

[Peter_SSs]

.. or could it be one of these?

For the future, so we do not have to guess so much what you mean please consider usingXL2BB for providing sample data & expected results to make it easier for helpers to understand just what you have & where it is and also what you want & where it is to be.
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)

KasperSSI.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1		January	February	March	April	May	June	July	August	September	October	November	December		Guess 1			Guess 2
2	Ac 1	97	25	5	24	54	23	34	66	98	96	36	63		Ac 1	97		Ac 1	January	97
3	Ac 2	1	38	25	56	36	45	58	24	66	9	15	70		Ac 2	1		Ac 2	January	1
4	Ac 3	59	6	25	24	54	54	4	38	16	63	34	18		Ac 3	59		Ac 3	January	59
5	Ac 4	11	67	22	81	11	27	51	96	18	69	17	69		Ac 4	11		Ac 4	January	11
6	Ac 5	60	99	57	74	73	17	74	40	54	70	6	14		Ac 5	60		Ac 5	January	60
7	Ac 6	61	84	40	64	67	66	71	26	44	1	66	69		Ac 6	61		Ac 6	January	61
8	Ac 7	55	55	2	27	20	86	47	35	65	67	33	90		Ac 7	55		Ac 7	January	55
9	Ac 8	35	59	30	94	22	24	45	50	36	94	63	89		Ac 8	35		Ac 8	January	35
10	Ac 9	71	56	97	91	67	18	47	8	23	77	64	28		Ac 9	71		Ac 9	January	71
11	Ac 10	28	81	20	17	7	37	28	14	46	90	44	95		Ac 10	28		Ac 10	January	28
12	Ac 11	66	48	99	35	94	50	7	87	75	69	21	78		Ac 11	66		Ac 11	January	66
13	Ac 12	80	29	33	70	35	60	29	48	12	66	65	19		Ac 12	80		Ac 12	January	80
14	Ac 13	71	24	98	19	60	49	67	9	25	38	46	81		Ac 13	71		Ac 13	January	71
15	Ac 14	94	79	2	86	67	86	58	83	34	30	83	39		Ac 14	94		Ac 14	January	94
16	Ac 15	6	95	99	5	16	34	61	11	30	20	19	76		Ac 15	6		Ac 15	January	6
17	Ac 16	19	58	84	66	93	75	4	63	25	98	2	23		Ac 16	19		Ac 16	January	19
18	Ac 17	55	98	51	59	12	60	3	71	96	52	43	20		Ac 17	55		Ac 17	January	55
19	Ac 18	20	29	88	27	50	54	21	89	22	39	9	31		Ac 18	20		Ac 18	January	20
20	Ac 19	19	4	78	20	29	40	95	22	85	60	66	61		Ac 19	19		Ac 19	January	19
21	Ac 20	83	96	42	55	32	91	16	48	19	59	37	36		Ac 20	83		Ac 20	January	83
22	Ac 21	40	14	72	77	98	76	45	59	7	18	2	49		Ac 21	40		Ac 21	January	40
23	Ac 22	18	50	92	70	90	88	15	19	43	54	18	26		Ac 22	18		Ac 22	January	18
24	Ac 23	54	62	77	38	31	41	46	99	31	20	73	67		Ac 23	54		Ac 23	January	54
25	Ac 24	93	45	68	62	76	44	9	5	55	21	87	7		Ac 24	93		Ac 24	January	93
26	Ac 25	34	34	79	26	32	66	40	61	80	32	69	90		Ac 25	34		Ac 25	January	34
27	Ac 26	58	31	70	61	87	1	81	94	87	6	97	2		Ac 26	58		Ac 26	January	58
28	Ac 27	75	75	91	93	76	52	60	93	4	91	85	8		Ac 27	75		Ac 27	January	75
29	Ac 28	42	75	92	22	15	56	48	46	41	95	30	61		Ac 28	42		Ac 28	January	42
30	Ac 29	63	49	31	3	58	40	79	27	46	20	8	80		Ac 29	63		Ac 29	January	63
31															Ac 1	25		Ac 1	February	25
32															Ac 2	38		Ac 2	February	38
33															Ac 3	6		Ac 3	February	6
34															Ac 4	67		Ac 4	February	67
35															Ac 5	99		Ac 5	February	99
Sheet1

Cell Formulas
Range	Formula
O2:P349	O2	=HSTACK(TOCOL(IF(SEQUENCE(,12),A2:A30),,1),TOCOL(B2:M30,,1))
R2:T349	R2	=TEXTSPLIT(TEXTJOIN(":",,TOCOL(IF(SEQUENCE(,12),A2:A30&"|"&B1:M1&"|"&B2:M30),,1)),"|",":")
Dynamic array formulas.

 

[KasperSSI]

Something along these lines?

=HSTACK(TOCOL(IF(SEQUENCE(,12),A2:A30),,TRUE),TOCOL(IF(SEQUENCE(ROWS(A2:A30)),B1:M1),,TRUE))

Thanks - that worked for what i asked for

 

[KasperSSI]

.. or could it be one of these?

For the future, so we do not have to guess so much what you mean please consider usingXL2BB for providing sample data & expected results to make it easier for helpers to understand just what you have & where it is and also what you want & where it is to be.
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)

KasperSSI.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1		January	February	March	April	May	June	July	August	September	October	November	December		Guess 1			Guess 2
2	Ac 1	97	25	5	24	54	23	34	66	98	96	36	63		Ac 1	97		Ac 1	January	97
3	Ac 2	1	38	25	56	36	45	58	24	66	9	15	70		Ac 2	1		Ac 2	January	1
4	Ac 3	59	6	25	24	54	54	4	38	16	63	34	18		Ac 3	59		Ac 3	January	59
5	Ac 4	11	67	22	81	11	27	51	96	18	69	17	69		Ac 4	11		Ac 4	January	11
6	Ac 5	60	99	57	74	73	17	74	40	54	70	6	14		Ac 5	60		Ac 5	January	60
7	Ac 6	61	84	40	64	67	66	71	26	44	1	66	69		Ac 6	61		Ac 6	January	61
8	Ac 7	55	55	2	27	20	86	47	35	65	67	33	90		Ac 7	55		Ac 7	January	55
9	Ac 8	35	59	30	94	22	24	45	50	36	94	63	89		Ac 8	35		Ac 8	January	35
10	Ac 9	71	56	97	91	67	18	47	8	23	77	64	28		Ac 9	71		Ac 9	January	71
11	Ac 10	28	81	20	17	7	37	28	14	46	90	44	95		Ac 10	28		Ac 10	January	28
12	Ac 11	66	48	99	35	94	50	7	87	75	69	21	78		Ac 11	66		Ac 11	January	66
13	Ac 12	80	29	33	70	35	60	29	48	12	66	65	19		Ac 12	80		Ac 12	January	80
14	Ac 13	71	24	98	19	60	49	67	9	25	38	46	81		Ac 13	71		Ac 13	January	71
15	Ac 14	94	79	2	86	67	86	58	83	34	30	83	39		Ac 14	94		Ac 14	January	94
16	Ac 15	6	95	99	5	16	34	61	11	30	20	19	76		Ac 15	6		Ac 15	January	6
17	Ac 16	19	58	84	66	93	75	4	63	25	98	2	23		Ac 16	19		Ac 16	January	19
18	Ac 17	55	98	51	59	12	60	3	71	96	52	43	20		Ac 17	55		Ac 17	January	55
19	Ac 18	20	29	88	27	50	54	21	89	22	39	9	31		Ac 18	20		Ac 18	January	20
20	Ac 19	19	4	78	20	29	40	95	22	85	60	66	61		Ac 19	19		Ac 19	January	19
21	Ac 20	83	96	42	55	32	91	16	48	19	59	37	36		Ac 20	83		Ac 20	January	83
22	Ac 21	40	14	72	77	98	76	45	59	7	18	2	49		Ac 21	40		Ac 21	January	40
23	Ac 22	18	50	92	70	90	88	15	19	43	54	18	26		Ac 22	18		Ac 22	January	18
24	Ac 23	54	62	77	38	31	41	46	99	31	20	73	67		Ac 23	54		Ac 23	January	54
25	Ac 24	93	45	68	62	76	44	9	5	55	21	87	7		Ac 24	93		Ac 24	January	93
26	Ac 25	34	34	79	26	32	66	40	61	80	32	69	90		Ac 25	34		Ac 25	January	34
27	Ac 26	58	31	70	61	87	1	81	94	87	6	97	2		Ac 26	58		Ac 26	January	58
28	Ac 27	75	75	91	93	76	52	60	93	4	91	85	8		Ac 27	75		Ac 27	January	75
29	Ac 28	42	75	92	22	15	56	48	46	41	95	30	61		Ac 28	42		Ac 28	January	42
30	Ac 29	63	49	31	3	58	40	79	27	46	20	8	80		Ac 29	63		Ac 29	January	63
31															Ac 1	25		Ac 1	February	25
32															Ac 2	38		Ac 2	February	38
33															Ac 3	6		Ac 3	February	6
34															Ac 4	67		Ac 4	February	67
35															Ac 5	99		Ac 5	February	99
Sheet1

Cell Formulas
Range	Formula
O2:P349	O2	=HSTACK(TOCOL(IF(SEQUENCE(,12),A2:A30),,1),TOCOL(B2:M30,,1))
R2:T349	R2	=TEXTSPLIT(TEXTJOIN(":",,TOCOL(IF(SEQUENCE(,12),A2:A30&"|"&B1:M1&"|"&B2:M30),,1)),"|",":")
Dynamic array formulas.

Some how, the second formula (textjoin) does not work - I get a CALC error. Saying text is to long?

I am on a EU version - dont know of any of the signs | or : is different ? i know US used , where EU use ;

is i posible to download a workbook example?

kasper

 

[Peter_SSs]

the second formula (textjoin) does not work - I get a CALC error. Saying text is to long?

TEXTJOIN has a limit of 32,767 characters. I assume then that your actual range is much bigger than the A1:M30 you described in post 1, or else the data in each cell is quite long.

is i posible to download a workbook example?

You cannot attach an actual file in the forum but you can give sample data using XL2BB as I did above.
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)

However, if @hagia_sofia's suggestion does what you want, you probably don't need to provide anything here.