SOLVED

# Duplicate rows in order without VBA

Copper Contributor

# 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:

4 Replies

# Re: Duplicate rows in order without VBA

alternatively:
=VSTACK(B3:C7,B3:C7)
best response confirmed by QW5314 (Copper Contributor)
Solution

# Re: Duplicate rows in order without VBA

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

# Re: Duplicate rows in order without VBA

Thank you Sir!! Worked like a charm.

# Re: Duplicate rows in order without VBA

You're welcome! :)
1 best response

Accepted Solutions
best response confirmed by QW5314 (Copper Contributor)
Solution

# Re: Duplicate rows in order without VBA

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