# Convert single row to multiple rows based on data in columns



## kalaniho (Dec 15, 2022)

I have the following table of data:


ProductDescriptionComponent 1Component 1 QtyComponent 2Component 2 QtyComponent 3Component 3 QtyComponent 4Component 4 QtyKIT-1KIT-1 descriptionCOMP-A1COMP-B1KIT-2KIT-2 descriptionCOMP-C2COMP-D2COMP-E2KIT-3KIT-3 descriptionCOMP-F3COMP-G3COMP-H3COMP-I3

And I need to convert it to the following:


ProductDescriptionComponentComponent QtyKIT-1KIT-1 descriptionCOMP-A1KIT-1KIT-1 descriptionCOMP-B1KIT-2KIT-2 descriptionCOMP-C2KIT-2KIT-2 descriptionCOMP-D2KIT-2KIT-2 descriptionCOMP-E2KIT-3KIT-3 descriptionCOMP-F3KIT-3KIT-3 descriptionCOMP-G3KIT-3KIT-3 descriptionCOMP-H3KIT-3KIT-3 descriptionCOMP-I3

Anyone know of this is possible using formulas?


----------



## Kerryx (Dec 16, 2022)

This is very easily done in Power query, import table to Power query, select Columns Component 1, Comp2, Comp3,Comp 4 and then click on Unpivot Only Selected columns

```
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Description", type text}, {"Component 1", type text}, {"Component 1 Qty", Int64.Type}, {"Component 2", type text}, {"Component 2 Qty", Int64.Type}, {"Component 3", type text}, {"Component 3 Qty", Int64.Type}, {"Component 4", type text}, {"Component 4 Qty", Int64.Type}}),
    #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Changed Type", {"Component 1", "Component 2", "Component 3", "Component 4"}, "Attribute", "Value"),
    #"Reordered Columns" = Table.ReorderColumns(#"Unpivoted Only Selected Columns",{"Product", "Description", "Attribute", "Value", "Component 1 Qty", "Component 2 Qty", "Component 3 Qty", "Component 4 Qty"})
in
    #"Reordered Columns"
```


----------



## shinigamilight (Dec 16, 2022)

```
Sub testing10101()
            Dim wk1, wk2 As Worksheet
            Set wk1 = Sheets("sheet29")
            Set wk2 = Sheets("sheet32")
            Dim k, i, j As Integer
            Dim lr As Long
            Dim store As String
            Dim repeat As Long
            ' k = row, i = column, store = repetition of kit column
            lr = wk1.Cells(Rows.Count, 1).End(xlUp).Row
            lc = wk1.Cells(1, Columns.Count).End(xlToLeft).Column
            For k = 2 To lr
              store = WorksheetFunction.CountA(Range(Cells(k, 3), Cells(k, lc))) / 2
                    For repeat = 1 To store
                            Range(Cells(k, 1), Cells(k, 2)).Copy wk2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
                    Next repeat
                            For i = 3 To lc Step 2
                                   If Cells(k, i) <> "" Then
                                   Range(Cells(k, i), Cells(k, i + 1)).Copy wk2.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0)
                                   End If
                            Next i
            Next k
            wk2.UsedRange.Columns("A:C").AutoFit

End Sub
```

change the sheet names in set wk1 and wk2. wk 1 is the data sheet and wk2 is the output sheet.


----------



## Alex Blakenburg (Dec 16, 2022)

Kerryx said:


> This is very easily done in Power query, import table to Power query, select Columns Component 1, Comp2, Comp3,Comp 4 and then click on Unpivot Only Selected columns


Not as easily as you might think. Your output still has multiple columns for Qty and you have the rows repeating all columns for each of the Qty's eg Kit 1 has the same line twice, Kit 3 has the same line 4 times.

The below is a bit ugly and I am sure there will be better ways but it will get the requested result.
(It is dependant on each Qty column having the word "Qty" at the end of the column heading (case sensitive)

I have left the Table name as Table1 but would prefer to give it a meaninful name.


```
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Product", "Description"}, "Attribute", "Value"),
    #"Added Conditional Column" = Table.AddColumn(#"Unpivoted Other Columns", "Custom", each if Text.EndsWith([Attribute], "Qty") then "Qty" else "Category"),
    #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"Attribute"}),
    #"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 1, 1, Int64.Type),
    #"Pivoted Column" = Table.Pivot(#"Added Index", List.Distinct(#"Added Index"[Custom]), "Custom", "Value"),
    #"Filled Down" = Table.FillDown(#"Pivoted Column",{"Category"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Qty] <> null)),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"Index"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns1",{{"Product", type text}, {"Description", type text}, {"Category", type text}, {"Qty", Int64.Type}})
in
    #"Changed Type"
```

Book1ABCD28ProductDescriptionCategoryQty29KIT-1KIT-1 descriptionCOMP-A130KIT-1KIT-1 descriptionCOMP-B131KIT-2KIT-2 descriptionCOMP-C232KIT-2KIT-2 descriptionCOMP-D233KIT-2KIT-2 descriptionCOMP-E234KIT-3KIT-3 descriptionCOMP-F335KIT-3KIT-3 descriptionCOMP-G336KIT-3KIT-3 descriptionCOMP-H337KIT-3KIT-3 descriptionCOMP-I3Sheet1


----------



## Fluff (Dec 16, 2022)

Or with a formula
Fluff.xlsmABCDEFGHIJ1ProductDescriptionComponent 1Component 1 QtyComponent 2Component 2 QtyComponent 3Component 3 QtyComponent 4Component 4 Qty2KIT-1KIT-1 descriptionCOMP-A1COMP-B13KIT-2KIT-2 descriptionCOMP-C2COMP-D2COMP-E24KIT-3KIT-3 descriptionCOMP-F3COMP-G3COMP-H3COMP-I35678910111213KIT-1KIT-1 descriptionCOMP-A114KIT-1KIT-1 descriptionCOMP-B115KIT-2KIT-2 descriptionCOMP-C216KIT-2KIT-2 descriptionCOMP-D217KIT-2KIT-2 descriptionCOMP-E218KIT-3KIT-3 descriptionCOMP-F319KIT-3KIT-3 descriptionCOMP-G320KIT-3KIT-3 descriptionCOMP-H321KIT-3KIT-3 descriptionCOMP-I322MainCell FormulasRangeFormulaA13:D21A13=HSTACK(TOCOL(IF(CHOOSECOLS(C2:J10,1,3,5,7)="",x,A2:A10),2),TOCOL(IF(CHOOSECOLS(C2:J10,1,3,5,7)="",x,B2:B10),2),WRAPROWS(TOCOL(C2:J10,1),2))Dynamic array formulas.


----------

