Forum Discussion

Corder's avatar
Corder
Copper Contributor
Mar 28, 2022
Solved

Formula to Duplicate Entire Rows Based on Cell Value

Hello everyone!   I'm trying to create a formula that will duplicate an entire row based on a cell's value. Below is a mock photo of what I am hoping to accomplish:     The formula would g...
excel
Formulas and Functions
  • OliverScheurich's avatar
    OliverScheurich
    Mar 28, 2022

    Corder 

    Sub companyemployees()

Dim i As Double
Dim j As Double
Dim z As Double
Dim w As Double

For i = 2 To 1000

j = Cells(i, 2).Value

For z = 2 To j + 1

Cells(z + w, 4).Value = Cells(i, 1).Value
Cells(z + w, 5).Value = Cells(i, 2).Value

Next z
w = w + z - 2

Next i

End Sub

     

    Maybe with these lines of VBA code. Click the button in cell G2 in the attached file to start the macro.

Starrysky1988's avatar
Starrysky1988
Iron Contributor
Mar 28, 2022

Corder 

If you are using beta version, you may write the formula as below with 1 helper formula behind.

 

 

 

LaurieBluegator's avatar
LaurieBluegator
Copper Contributor
Nov 05, 2023

Starrysky1988 Thank you for this example.  The only problem is that Column D & E are bringing back numbers instead of my values from columns A.  I think I am missing the formula that you have down in the step area? Also noticed it is throwing an error when I have a 1 in column B.  Use case is I would like the email in column A repeated as many times as the value in column B.

I need a list of emails repeated as many times as in column B

 

  • djclements's avatar
    djclements
    Bronze Contributor
    Nov 06, 2023

    LaurieBluegator With MS365, you could try something like this to generate a repeating list of email addresses from column A, based on the values in column B:

     

    =LET(arr, A2:B6,
REDUCE("Email", SEQUENCE(ROWS(arr)), LAMBDA(v,n,
   VSTACK(v, IF(SEQUENCE(INDEX(arr, n, 2)), INDEX(arr, n, 1))))))

     

    Or, if you also need the values in column B to repeat alongside the email addresses, try:

     

    =LET(arr, A2:B6,
REDUCE({"Email","Rows"}, SEQUENCE(ROWS(arr)), LAMBDA(v,n,
   VSTACK(v, IF(SEQUENCE(INDEX(arr, n, 2)), INDEX(arr, n, 0))))))

     

    Adjust the range reference (A2:B6) as needed.

     

    UPDATE: simplified/optimized methods include...

     

    =TOCOL(IFS(B2:B6>=SEQUENCE(, MAX(B2:B6)), A2:A6), 2)

     

    Or, to repeat both columns:

     

    =CHOOSEROWS(A2:B6, TOCOL(IFS(B2:B6>=SEQUENCE(, MAX(B2:B6)), SEQUENCE(ROWS(A2:A6))), 2))

     

    Cheers! 🙂

    • LaurieBluegator's avatar
      LaurieBluegator
      Copper Contributor
      Nov 09, 2023

      djclements Thank you so much.  I appreciate the options.  I will give these a try.

Resources