CODEWITHSUNDEEP
exceldaxpowerquery
Baldy
Baldy

Reputation: 3

Expanding rows in Excel

There is probably a simple solution to this, but I've been banging my head against a wall. I have about two hundred rows where the first five columns are the same and the final column has a number. I want to expand the table to have x amount of duplicate rows. X being the number in the final column. I thought I could unpivot, but that doesn't seem to be working. Any solutions I'm unaware of? For example:

Row 1.  a   b   c   5 <br>
Row 2  aa   bb  cc  3

Becomes:

a   b   c
a   b   c
a   b   c
a   b   c
a   b   c

aa  bb  cc
aa  bb  cc
aa  bb  cc

Upvotes: 0

Views: 204

Answers (2)

Olly
Olly

Reputation: 7891

You can use Power Query (Get & Transform Data).

  • Ensure your source data is formatted as a table.

  • Change "Table1" and "Column6" names to suit your data.

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column6", Int64.Type}}),
    #"List Numbers" = Table.TransformColumns(#"Changed Type", {"Column6", each List.Numbers(1,_,1), type list}),
    #"Expanded Rows" = Table.ExpandListColumn(#"List Numbers", "Column6")
in
    #"Expanded Rows"

Note that this is M (Power Query language), not DAX.

Upvotes: 2

Onur Tekir
Onur Tekir

Reputation: 250

You can write a simple macro. You can see sample macro VB code below.

Sub Create()

Dim count As Integer
Dim col1 As String       
Dim col2 As String
Dim col3 As String

Dim row_counter As Integer

row_counter = 1

For x = 1 To 2

    count = Sheet1.Cells(x, "D")
    col1 = Sheet1.Cells(x, "A")
    col2 = Sheet1.Cells(x, "B")
    col3 = Sheet1.Cells(x, "C")

    For y = 1 To count

        Sheet2.Cells(row_counter, "A") = col1
        Sheet2.Cells(row_counter, "B") = col2
        Sheet2.Cells(row_counter, "C") = col3
        row_counter = row_counter + 1

    Next y
Next x
End Sub

Upvotes: 0

Related Questions