Formula to repeat a previous row multiple times based on a number in a cell

Question

I have multiple lines of data that refer to x number of females and x number of males per line.

I need to break them out into individual records (1 row for each). Is there an excel formula that would look at the first row and based on the number in column D, return that number of rows and the data in a, b, c is same, however, for columns e, f, g, h, replace that many records with a 1 all in another sheet?

See screen image. There are 9 rows with a 1 in level 1, 17 with a 1 in level 2, etc. I have tried basic formulas combos that work but are time-consuming and there are 1342 rows that need to be broken into individual records. enter image description here

Here is the sheet after the unpivot you suggested David. Table after unpivot

codeandoutput

CODE FOR 5 LEVELS with code for slow issues Sub CreateIndividualRecordsLevel5() Dim i As Long Dim a As Long Dim b As Long Dim c As Long Dim d As Long Dim e As Long Dim n As Long Dim instancesInRow As Long Dim level1 As Long Dim level2 As Long Dim level3 As Long Dim level4 As Long Dim level5 As Long Dim wb As Workbook Dim ws As Worksheet

Application.ScreenUpdating = False
Application.Calculation = xlCalculationAutomatic

Application.DisplayStatusBar = False
Application.EnableEvents = False

Set wb = ThisWorkbook
Set ws = wb.Worksheets("WHITE")

n = 0


' Outer loop 'goes' through your original table and for each cell in
' column D checks how many instances there are.
For i = 2 To 523

        instancesInRow = ws.Cells(i, 4).Value
        level1 = ws.Cells(i, 5).Value
        level2 = ws.Cells(i, 6).Value
        level3 = ws.Cells(i, 7).Value
        level4 = ws.Cells(i, 8).Value
        level5 = ws.Cells(i, 9).Value

        ' Inner loop 'knows' how many times it needs to loop-copy the same
        ' row into the new table/worksheet.
        For a = 1 To level1
                n = n + 1
                ws.Range("J" & n & ":M" & n).Value = ws.Range("A" & i & ":I" & i).Value
                ws.Range("N" & n).Value = 1
                ws.Range("O" & n).Value = 0
                ws.Range("P" & n).Value = 0
                ws.Range("Q" & n).Value = 0
                ws.Range("R" & n).Value = 0
        Next a

        For b = 1 To level2
                n = n + 1
                ws.Range("J" & n & ":M" & n).Value = ws.Range("A" & i & ":I" & i).Value
                ws.Range("N" & n).Value = 0
                ws.Range("O" & n).Value = 1
                ws.Range("P" & n).Value = 0
                ws.Range("Q" & n).Value = 0
                ws.Range("R" & n).Value = 0
        Next b

        For c = 1 To level3
                n = n + 1
                ws.Range("J" & n & ":M" & n).Value = ws.Range("A" & i & ":I" & i).Value
                ws.Range("N" & n).Value = 0
                ws.Range("O" & n).Value = 0
                ws.Range("P" & n).Value = 1
                ws.Range("Q" & n).Value = 0
                ws.Range("R" & n).Value = 0
        Next c

        For d = 1 To level4
                n = n + 1
                ws.Range("J" & n & ":M" & n).Value = ws.Range("A" & i & ":I" & i).Value
                ws.Range("N" & n).Value = 0
                ws.Range("O" & n).Value = 0
                ws.Range("P" & n).Value = 0
                ws.Range("Q" & n).Value = 1
                ws.Range("R" & n).Value = 0
        Next d

        For e = 1 To level5
                n = n + 1
                ws.Range("J" & n & ":M" & n).Value = ws.Range("A" & i & ":I" & i).Value
                ws.Range("N" & n).Value = 0
                ws.Range("O" & n).Value = 0
                ws.Range("P" & n).Value = 0
                ws.Range("Q" & n).Value = 0
                ws.Range("R" & n).Value = 1

        Next e


                ' Counter n counts how many rows you have copied so far,
                'in order to know in which row to copy to the new range

Next i

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

Application.DisplayStatusBar = True
Application.EnableEvents = True

End Sub

I am not sure why you ask for a formula if it can return multiple rows, as to my best knowledge a formula affects only that cell's value that it is entered into. What you need is an unpivot functionality I believe, try to search for that for your Excel version. — Dávid Laczkó
Your suggestion was perfect!. THank you. However, I need to analyze the data, but because it is x number of females or x number of males per row, SPSS will count the rows, and the data does not come in "individual student level." I performed an unpivot in excel and it broke the data down by levels in one column and the number of females or males in the next column, but I need those to be individual lines, I need 9 rows of level 1 county female PENF and I tried to do another unpivot from the new sheet and it doesn't break out the 9 into individual rows. Any other suggestions David? — ChristyL

Unknown Unknown · Accepted Answer · 2019-06-15T22:36:57

I'm sorry I made a mistake and it took a bit longer. Here we go:

Check the image with the result here >>> enter image description here

    Private Sub testcopyrow()
    Dim i As Long
    Dim a As Long
    Dim b As Long
    Dim c As Long
    Dim d As Long
    Dim e As Long
    Dim n As Long
    Dim instancesInRow As Long
    Dim level1 As Long
    Dim level2 As Long
    Dim level3 As Long
    Dim level4 As Long
    Dim wb As Workbook
    Dim ws As Worksheet

    Set wb = ThisWorkbook
    Set ws = wb.Worksheets("SplitRowsByInstances")

    n = 0

    ' Outer loop 'goes' through your original table and for each cell in
    ' column D checks how many instances there are.
    For i = 2 To 7

            instancesInRow = ws.Cells(i, 4).Value
            level1 = ws.Cells(i, 5).Value
            level2 = ws.Cells(i, 6).Value
            level3 = ws.Cells(i, 7).Value
            level4 = ws.Cells(i, 8).Value

            ' Inner loop 'knows' how many times it needs to loop-copy the same
            ' row into the new table/worksheet.
            For a = 1 To level1
                    n = n + 1
                    ws.range("J" & n & ":M" & n).Value = ws.range("A" & i & ":H" & i).Value
                    ws.range("N" & n).Value = 1
                    ws.range("O" & n).Value = 0
                    ws.range("P" & n).Value = 0
                    ws.range("Q" & n).Value = 0
            Next a

            For b = 1 To level2
                    n = n + 1
                    ws.range("J" & n & ":M" & n).Value = ws.range("A" & i & ":H" & i).Value
                    ws.range("N" & n).Value = 0
                    ws.range("O" & n).Value = 1
                    ws.range("P" & n).Value = 0
                    ws.range("Q" & n).Value = 0
            Next b

            For c = 1 To level3
                    n = n + 1
                    ws.range("J" & n & ":M" & n).Value = ws.range("A" & i & ":H" & i).Value
                    ws.range("N" & n).Value = 0
                    ws.range("O" & n).Value = 0
                    ws.range("P" & n).Value = 1
                    ws.range("Q" & n).Value = 0
            Next c

            For d = 1 To level4
                    n = n + 1
                    ws.range("J" & n & ":M" & n).Value = ws.range("A" & i & ":H" & i).Value
                    ws.range("N" & n).Value = 0
                    ws.range("O" & n).Value = 0
                    ws.range("P" & n).Value = 0
                    ws.range("Q" & n).Value = 1
            Next d


                    ' Counter n counts how many rows you have copied so far,
                    'in order to know in which row to copy to the new range

    Next i

    End Sub

Let me know if I helped!

Formula to repeat a previous row multiple times based on a number in a cell

1 Answers