Forum Discussion
Solved
Duplicate rows in order without VBA
I need to duplicate rows (without VBA), based on a cell value (column C in the example below). I found the formula below and it works in duplicating the rows, but I need the rows in the original order, not as per the actual results below:
Formula:
=SORT(CHOOSEROWS(B3:C7, TOCOL(IFS(C3:C7>=SEQUENCE(, MAX(C3:C7),,1), SEQUENCE(ROWS(B3:B7))), 2)))
Rows to duplicate:
Actual Results:
Expected results:
QW5314 Try removing the SORT function, then set the optional [scan_by_column] argument of TOCOL to TRUE or 1:
=CHOOSEROWS(B3:C7, TOCOL(IFS(C3:C7>=SEQUENCE(, MAX(C3:C7)), SEQUENCE(ROWS(B3:B7))), 2, 1))I'm assuming the number of repeats specified in column C won't always be the same for each record, otherwise this formula might be a bit excessive. Cheers!
QW5314 Try removing the SORT function, then set the optional [scan_by_column] argument of TOCOL to TRUE or 1:
=CHOOSEROWS(B3:C7, TOCOL(IFS(C3:C7>=SEQUENCE(, MAX(C3:C7)), SEQUENCE(ROWS(B3:B7))), 2, 1))I'm assuming the number of repeats specified in column C won't always be the same for each record, otherwise this formula might be a bit excessive. Cheers!
- Thank you Sir!! Worked like a charm.
- You're welcome! 🙂
- alternatively:
=VSTACK(B3:C7,B3:C7)
