Automatically repeating rows based on repeat factor cell (without VBA)

[matt0020190]

Hello experts, hope you are all well!

I have intermediate skills with excel but this one has stumped me.

I cannot use any Macros due to workplace policy so this needs to be done with formulas.

I have a list of names that needs to repeat into another table based on a cell repeat factor i.e. 3 times, 4 times etc

I attach an example spreadsheet and the desired output I am after.

The original table has names, a date range per name (ie. May to July) and a calculated repeat factor based on this date range. For example May to July is 3 months inclusive. Therefore the number of rows to display in the new table is 3, one row for each relevant month.

Can anyone help me do this with formula please?

Would be most appreciated!

Thanks

Matt

[#2]

@matt0020190 You expected result data do not match with input data. Just a clue, you may try something like-

=DROP(REDUCE("",D2:D4,LAMBDA(a,x,VSTACK(a,HSTACK(
IF(SEQUENCE(x),EDATE(CHOOSEROWS(B2:B4,ROW(x)-1),SEQUENCE(x)-1),""),
IF(SEQUENCE(x),CHOOSEROWS(A2:A4,(ROW(x)-(MIN(ROW(D2:D4))-1))),""))))),1)

[#3]

Hey thanks Harun, this is great!
I cant see where it does not match up, looks fine to me?
Great work!

[#4]

@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)...

[#5]

@djclements 

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

[#6]

@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!

[#7]

@djclements 

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!

[#8 · Accepted solution]

@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 ).

[#9]

Sorry for the delay - perfect thanks so much!