Forum Discussion
Automatically repeating rows based on repeat factor cell (without VBA)
matt0020190 There are a couple of things with this new layout/structure that I would do differently. Your dates are being entered as Text, which is wreaking havoc with the Duration formula, and the repeated use of VLOOKUP to pull data into the Ongoing Projects worksheet can be simplified with one FILTER formula.
In the attached spreadsheet, I've formatted your Lookup Data as a structured Excel table (Insert > Table), added data validation and conditional formatting to ensure data integrity, and used a different formula to calculate the duration (for more info, see: https://exceljet.net/formulas/get-months-between-dates ).
matt0020190 To repeat the names:
=TOCOL(IFS(D2:D4>=SEQUENCE(,MAX(D2:D4)), A2:A4), 2)
To generate the sequential start dates:
=LET(
cols, SEQUENCE(,MAX(D2:D4)),
test, D2:D4>=cols,
EDATE(TOCOL(IFS(test, B2:B4), 2), TOCOL(IFS(test, cols-1), 2))
)
In the attached workbook, there's also an attempt to duplicate the "desired" output in a single cell dynamic array formula, but it's unclear where the data is coming from for Category and Days/Month (1, 7 and 3 are meaningless)...
Ah I see what you mean. No sorry the days/month column, ID and category remain the same. All I am trying to achieve is basically create a new row for every month concerned.
Harun's example works fine to be honest after expanding the dataset to the other columns.
Only thing that would be good however would be if the data could be ordered by month opposed to sequence / name. Is that possible using SORT?I have attached a new example to hopefully make it easier to understand
matt0020190 Yes, just wrap the results in SORT (since the dates are in the first column, there is no need to specify a sort_index). Feel free to use whichever method you prefer. DROP / REDUCE / VSTACK can be shorter/easier to write and works well with smaller data sets. Non-iterative methods like TOCOL / IFS / SEQUENCE will typically perform better with larger data sets.
Here's how I would write it with the updated sample workbook:
=LET( data, A2:G4, n, TAKE(data,, -1), cols, SEQUENCE(, MAX(n)), test, n >= cols, rowId, TOCOL(IFS(test, SEQUENCE(ROWS(n))), 2), repeat_data, INDEX(data, rowId, {5,2,3,1,4}), SORT(HSTACK( EDATE(TAKE(repeat_data,, 1), TOCOL(IFS(test, cols-1), 2)), DROP(repeat_data,, 1) )) )See attached... Cheers!
Hey, this is great thanks! The only issue that now comes out of both formula options is handling empty rows. Please see attached example.
Your method works well it seems and orders by month with the SORT function added, however the duration row highlighted seems to cause VALUE# errors further down.Is there a way to remove these errors please?
Thanks again!
