Create a table using dynamic rows and columns

vampsthevampyre

New Member
Joined
Apr 29, 2016
Messages
5
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
ABCDEFGHIJKLMNOPQR
2Dates Produced
3DateProductRow No.Unique IDUnique ProductTimes Produced123456789
410/09/2020AAA41AAA910/09/202015/10/202027/10/202028/10/202029/10/202002/12/202014/12/202015/12/202016/12/2020
510/09/2020BBB52BBB310/09/202027/10/202029/10/2020
610/09/2020CCC63CCC410/09/202028/10/202029/10/202014/12/2020
715/10/2020AAA74DDD4
815/10/2020DDD
927/10/2020AAA
1027/10/2020BBBNo of days between production runs (Latest date to historic dates)
1127/10/2020DDD4AAA91182411825
1228/10/2020AAA5BBB3233
1328/10/2020CCC6CCC432134
1429/10/2020AAA7DDD4
1529/10/2020BBB
1629/10/2020CCC
1729/10/2020DDD
1802/12/2020AAA
1902/12/2020DDD
2014/12/2020AAA
2114/12/2020CCC
2215/12/2020AAA
2316/12/2020AAA
24
Sheet1
Cell Formulas
RangeFormula
E4:E7E4=ROW()+F4#-1
F4:F7F4=SEQUENCE(COUNTA(G4#),1,1,1)
G4:G7G4=UNIQUE(Table1[Product])
H4:H7H4=COUNTIF(Table1[Product],G4#)
I3:Q3I3=SEQUENCE(1,MAX(H4#),1,1)
I4:Q6I4=IFERROR(INDEX(Table1[Date],SMALL(IF(Table1[Product] = G4,ROW(Table1[Product])-ROW(INDEX(Table1[Product],1,1))+1),$I$3#)),"")
E11:E14,G11:H14E11=E4#
I11:P11,I13:K13,I12:J12I11=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.
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,124
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,547
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Apr 29, 2016
Messages
5
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
ABCDEFGHIJKLMNOPQR
2Dates Produced
3DateProductRow No.Unique IDUnique ProductTimes Produced123456789
410/09/2020AAA41AAA910/09/202015/10/202027/10/202028/10/202029/10/202002/12/202014/12/202015/12/202016/12/2020
510/09/2020BBB52BBB310/09/202027/10/202029/10/2020
610/09/2020CCC63CCC410/09/202028/10/202029/10/202014/12/2020
715/10/2020AAA74DDD4
815/10/2020DDD
927/10/2020AAA
1027/10/2020BBBNo of days between production runs (Latest date to historic dates)
1127/10/2020DDD4AAA91182411825
1228/10/2020AAA5BBB3233
1328/10/2020CCC6CCC432134
1429/10/2020AAA7DDD4
1529/10/2020BBB
1629/10/2020CCC
1729/10/2020DDD
1802/12/2020AAA
1902/12/2020DDD
2014/12/2020AAA
2114/12/2020CCC
2215/12/2020AAA
2316/12/2020AAA
24
Sheet1
Cell Formulas
RangeFormula
E4:E7E4=ROW()+F4#-1
F4:F7F4=SEQUENCE(COUNTA(G4#),1,1,1)
G4:G7G4=UNIQUE(Table1[Product])
H4:H7H4=COUNTIF(Table1[Product],G4#)
I3:Q3I3=SEQUENCE(1,MAX(H4#),1,1)
I4:Q6I4=IFERROR(INDEX(Table1[Date],SMALL(IF(Table1[Product] = G4,ROW(Table1[Product])-ROW(INDEX(Table1[Product],1,1))+1),$I$3#)),"")
E11:E14,G11:H14E11=E4#
I11:P11,I13:K13,I12:J12I11=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

New Member
Joined
Apr 29, 2016
Messages
5
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
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,129,539
Messages
5,636,903
Members
416,948
Latest member
Jkpang

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top