Create a table using dynamic rows and columns

[vampsthevampyre]

Can anybody please help,

I'm trying to create a dynamic table that has both dynamic rows and columns with its data coming from another table.

I have a table that contains different product (AAA-DDD) and the dates that they were produced. From this table I have created a list of unique products and the number of times they were produced. This then allowed me to create the date columns. I have a formula in I4 that reads the main table and populates the columns with the dates the product was produced.

Unfortunately I have not been able to find out how to get this to dynamically fill I5, I6 and I7. at the moment I have to manually drag this equation into these columns.

I then have another table that calculates the number of days between each time a product was produced, again I cannot get the formula in I11 to spill down to I14 (I manually dragged it down to I13 in the example.

My issue is that if you add product EEE to the table I currently have to manually drag the equations down to accommodate the extra products and I'm getting old and forgetful and prefer excel to do all the hard work and make me look good to the boss

Any help will be gratefully received

Regards
Ian

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
2									Dates Produced
3		Date	Product		Row No.	Unique ID	Unique Product	Times Produced	1	2	3	4	5	6	7	8	9
4		10/09/2020	AAA		4	1	AAA	9	10/09/2020	15/10/2020	27/10/2020	28/10/2020	29/10/2020	02/12/2020	14/12/2020	15/12/2020	16/12/2020
5		10/09/2020	BBB		5	2	BBB	3	10/09/2020	27/10/2020	29/10/2020
6		10/09/2020	CCC		6	3	CCC	4	10/09/2020	28/10/2020	29/10/2020	14/12/2020
7		15/10/2020	AAA		7	4	DDD	4
8		15/10/2020	DDD
9		27/10/2020	AAA
10		27/10/2020	BBB						No of days between production runs (Latest date to historic dates)
11		27/10/2020	DDD		4		AAA	9	1	1	8	24	1	1	8	25
12		28/10/2020	AAA		5		BBB	3	2	33
13		28/10/2020	CCC		6		CCC	4	32	1	34
14		29/10/2020	AAA		7		DDD	4
15		29/10/2020	BBB
16		29/10/2020	CCC
17		29/10/2020	DDD
18		02/12/2020	AAA
19		02/12/2020	DDD
20		14/12/2020	AAA
21		14/12/2020	CCC
22		15/12/2020	AAA
23		16/12/2020	AAA
24
Sheet1

Cell Formulas
Range	Formula
E4:E7	E4	=ROW()+F4#-1
F4:F7	F4	=SEQUENCE(COUNTA(G4#),1,1,1)
G4:G7	G4	=UNIQUE(Table1[Product])
H4:H7	H4	=COUNTIF(Table1[Product],G4#)
I3:Q3	I3	=SEQUENCE(1,MAX(H4#),1,1)
I4:Q6	I4	=IFERROR(INDEX(Table1[Date],SMALL(IF(Table1[Product] = G4,ROW(Table1[Product])-ROW(INDEX(Table1[Product],1,1))+1),$I$3#)),"")
E11:E14,G11:H14	E11	=E4#
I11:P11,I13:K13,I12:J12	I11	=IFERROR(NETWORKDAYS.INTL(INDIRECT(ADDRESS(E11,COLUMN(H11)+SEQUENCE(1,H11-1,H11-1,-1))),INDIRECT(ADDRESS(E11,COLUMN(H11)+SEQUENCE(1,H11-1,H11,-1))))-1,"")
Dynamic array formulas.

 

[StephenCrump]

A single array formula would be nice, but I don't think it's possible for either.

You can simplify I4: =TRANSPOSE(FILTER(Table1[Date],Table1[Product]=G4)). It's not necessary for the dataset posted, but you could wrap the FILTER in UNIQUE and/or SORT if necessary.

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Create a table using dynamic rows and columns
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[vampsthevampyre]

A single array formula would be nice, but I don't think it's possible for either.

You can simplify I4: =TRANSPOSE(FILTER(Table1[Date],Table1[Product]=G4)). It's not necessary for the dataset posted, but you could wrap the FILTER in UNIQUE and/or SORT if necessary.

Thanks for the reply and the nice equation, so much neater than my solution

Can anybody please help,

I'm trying to create a dynamic table that has both dynamic rows and columns with its data coming from another table.

I have a table that contains different product (AAA-DDD) and the dates that they were produced. From this table I have created a list of unique products and the number of times they were produced. This then allowed me to create the date columns. I have a formula in I4 that reads the main table and populates the columns with the dates the product was produced.

Unfortunately I have not been able to find out how to get this to dynamically fill I5, I6 and I7. at the moment I have to manually drag this equation into these columns.

I then have another table that calculates the number of days between each time a product was produced, again I cannot get the formula in I11 to spill down to I14 (I manually dragged it down to I13 in the example.

My issue is that if you add product EEE to the table I currently have to manually drag the equations down to accommodate the extra products and I'm getting old and forgetful and prefer excel to do all the hard work and make me look good to the boss

Any help will be gratefully received

Regards
Ian

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
2									Dates Produced
3		Date	Product		Row No.	Unique ID	Unique Product	Times Produced	1	2	3	4	5	6	7	8	9
4		10/09/2020	AAA		4	1	AAA	9	10/09/2020	15/10/2020	27/10/2020	28/10/2020	29/10/2020	02/12/2020	14/12/2020	15/12/2020	16/12/2020
5		10/09/2020	BBB		5	2	BBB	3	10/09/2020	27/10/2020	29/10/2020
6		10/09/2020	CCC		6	3	CCC	4	10/09/2020	28/10/2020	29/10/2020	14/12/2020
7		15/10/2020	AAA		7	4	DDD	4
8		15/10/2020	DDD
9		27/10/2020	AAA
10		27/10/2020	BBB						No of days between production runs (Latest date to historic dates)
11		27/10/2020	DDD		4		AAA	9	1	1	8	24	1	1	8	25
12		28/10/2020	AAA		5		BBB	3	2	33
13		28/10/2020	CCC		6		CCC	4	32	1	34
14		29/10/2020	AAA		7		DDD	4
15		29/10/2020	BBB
16		29/10/2020	CCC
17		29/10/2020	DDD
18		02/12/2020	AAA
19		02/12/2020	DDD
20		14/12/2020	AAA
21		14/12/2020	CCC
22		15/12/2020	AAA
23		16/12/2020	AAA
24
Sheet1

Cell Formulas
Range	Formula
E4:E7	E4	=ROW()+F4#-1
F4:F7	F4	=SEQUENCE(COUNTA(G4#),1,1,1)
G4:G7	G4	=UNIQUE(Table1[Product])
H4:H7	H4	=COUNTIF(Table1[Product],G4#)
I3:Q3	I3	=SEQUENCE(1,MAX(H4#),1,1)
I4:Q6	I4	=IFERROR(INDEX(Table1[Date],SMALL(IF(Table1[Product] = G4,ROW(Table1[Product])-ROW(INDEX(Table1[Product],1,1))+1),$I$3#)),"")
E11:E14,G11:H14	E11	=E4#
I11:P11,I13:K13,I12:J12	I11	=IFERROR(NETWORKDAYS.INTL(INDIRECT(ADDRESS(E11,COLUMN(H11)+SEQUENCE(1,H11-1,H11-1,-1))),INDIRECT(ADDRESS(E11,COLUMN(H11)+SEQUENCE(1,H11-1,H11,-1))))-1,"")
Dynamic array formulas.

opps sorry for the cross post Create a table using dynamic rows and columns will not happen again if I get a solution I will post it to the other site as well and vice versa

 

[vampsthevampyre]

Thanks to everybody who helped (@StephenCrump elegant update to my awful code) I've learnt a lot and now have to finally learn pivot tables a big thanks to P45cal who's power query (besides from needing a manual refresh from the data tab) automatically created the follow up tables perfectly. link to his solution here P45cal Power Query solution