• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Create a table using dynamic rows and columns

vampsthevampyre

New Member
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

OOPS just realised this thread is cross posted other thread is here https://www.mrexcel.com/board/threads/create-a-table-using-dynamic-rows-and-columns.1158384/

Regards
Ian
 

Attachments

Last edited:

Peter Bartholomew

Well-Known Member
I think that you are a bit stuck when it comes to creating 2D tables with a single array formula because many of the functions you use require single columns. 1D is reasonably possible.
Code:
= LET(
  productionDates, XLOOKUP(@Distinct2#, Distinct#, initialRuns)#,
  n, COUNT(productionDates),
  k, SEQUENCE(1,n-1, n, -1),
  nextRun, INDEX(productionDates, k),
  priorRun, INDEX(productionDates, k-1),
  NETWORKDAYS.INTL(priorRun+1, nextRun))
The 'fancy bit' is returning a range reference using XLOOKUP and adding the # to make it reference a dynamic range.
The first time I have had an opportunity to deploy that!!
 

Attachments

Peter Bartholomew

Well-Known Member
A slightly different approach that calculates the workdays between runs directly from the data table rather than the filtered production dates.
There is a function "LAMBDA" on beta test that could deliver the result tables with a formula that spills from single cell but it remains to be seen how much effort is justified simply to remove the need for relative referencing.
72984
 

Attachments

p45cal

Well-Known Member
…and a Power Query version.
The attached is a bit of a mess (and needs a lot of tidying up and streamlining) but duplicates your results and illustrates automatic table resizing: change the extent of the source data table by dragging the table grab-handle at bottom right, and increase the product range by copy/pasting new product names. You do need to refresh the tables either by clicking Refresh All in the Queries & Connections section of the Data tab of the ribbon or by right-clicking each table and choosing Refresh.
 

Attachments

vampsthevampyre

New Member
…and a Power Query version.
The attached is a bit of a mess (and needs a lot of tidying up and streamlining) but duplicates your results and illustrates automatic table resizing: change the extent of the source data table by dragging the table grab-handle at bottom right, and increase the product range by copy/pasting new product names. You do need to refresh the tables either by clicking Refresh All in the Queries & Connections section of the Data tab of the ribbon or by right-clicking each table and choosing Refresh.
Thanks @p45cal that solution works perfectly although I'm also looking into @Peter Bartholomew 's possible solution of the Lambda approach, unfortunately I do not have access to the beta version.

A special thanks to @pecoflyer for his helpful insight into my question and his contribution to not only answering the question but making a new member feel at home.

I have all ready posted @p45cal 's solution on the other forum and its link to this post, thanks to @Fluff13 I now read the FAQ's on all forums before posting.

maybe @pecoflyer could continue in his helpful manner and tell me how to mark this question as solved or even how to change the forum post title to include the word solved.

Once again thanks to everybody who contributed I now have a lot better understanding of how dynamic arrays work

cheers
Ian
 
Last edited:
Top