How to Transform One Row into Multiple Rows for Data Normalization in Excel Using Different Methods

To breakdown one row in Excel to multiple rows for normalization, you need to transform the data from a wide format to a long format. This means that each row will have only one value for each variable, instead of multiple values in different columns.

The basic theory behind this transformation is to make the data more suitable for analysis, especially when you want to perform calculations or grouping based on the variables. For example, if you have a row that contains the purchase items and quantities for each order, you may want to split it into multiple rows so that you can filter, sort, or aggregate the data by item or quantity.

There are different procedures to achieve this transformation, depending on the structure and complexity of your data. Here are some possible methods:

• Use the built-in Text to Columns feature to split the values in one cell into multiple cells, based on a delimiter such as comma, space, or dash. Then use the Paste Special > Transpose option to paste the values as rows instead of columns. You may need to repeat this process for each row that has multiple values in one cell.
• Use the Power Query Editor to unpivot the columns that have multiple values into rows. This is a more automated and flexible way to transform the data, especially if you have many columns or rows to deal with. You can access the Power Query Editor from the Data tab > Get & Transform Data group > From Table/Range. Then select the columns that you want to unpivot, right-click and choose Unpivot Columns. This will create a new table with the normalized data.
• Use a VBA macro to loop through the rows and columns and copy the values to a new sheet, based on a fixed value or a condition. This is a more advanced and customizable way to transform the data, but it requires some programming skills and knowledge of VBA syntax. You can find some examples of VBA codes online that can perform this task.

To illustrate these methods, let’s use a simple scenario as an example. Suppose you have a table like this:

The purchase items are saved as a string for each order, and it is impossible to filter or group by the item name or quantity. You want to transform the table to be like this:

Here are the steps to do this using the Text to Columns and Paste Transpose features:

• Select the Purchase Items column and go to Data tab > Data Tools group > Text to Columns.
• In the Convert Text to Columns Wizard, choose Delimited as the data type and click Next.
• In the Delimiters section, check Comma and Space as the delimiters and click Next.
• In the Column data format section, choose General as the data format and click Finish.
• You will see that the Purchase Items column is split into six columns, with each item and quantity in a separate cell.
• Select the six columns and copy them by pressing Ctrl + C.
• Select the first cell of the destination range, right-click and choose Paste Special.
• In the Paste Special dialog box, check Transpose and click OK.
• You will see that the values are pasted as rows instead of columns, with each order ID repeated for each item and quantity.
• Delete the original Purchase Items column and rename the new columns as Item and Quantity.

Here are the steps to do this using the Power Query Editor:

• Select any cell in the table and go to Data tab > Get & Transform Data group > From Table/Range.
• This will open the Power Query Editor window, where you can see a preview of your table.
• Select the Purchase Items column and go to Transform tab > Text Column group > Split Column > By Delimiter.
• In the Split Column by Delimiter dialog box, choose Comma as the delimiter and click OK.
• You will see that the Purchase Items column is split into three columns, with each item and quantity pair in a separate cell.
• Select the three columns and right-click and choose Unpivot Columns.
• You will see that the columns are unpivoted into rows, with each order ID repeated for each item and quantity pair.
• Delete the Attribute column and rename the Value column as Purchase Items.
• Select the Purchase Items column and go to Transform tab > Text Column group > Split Column > By Number of Characters.
• In the Split Column by Number of Characters dialog box, choose 1 as the number of characters to split by and click OK.
• You will see that the Purchase Items column is split into two columns, with each item and quantity in a separate cell.
• Rename the two columns as Item and Quantity.
• Go to Home tab > Close & Load group > Close & Load.
• This will create a new worksheet with the normalized table.

Here is an example of a VBA code that can do this using a loop:

Sub NormalizeData()
    Dim wsSource As Worksheet 'the worksheet with the original data
    Dim wsTarget As Worksheet 'the worksheet with the normalized data
    Dim i As Long 'the row index for the source data
    Dim j As Long 'the column index for the source data
    Dim k As Long 'the row index for the target data
    Dim arr As Variant 'the array to store the split values
    
    Set wsSource = Worksheets("Sheet1") 'change as needed
    Set wsTarget = Worksheets("Sheet2") 'change as needed
    
    k = 2 'start from the second row of the target sheet
    
    For i = 2 To wsSource.Cells(Rows.Count, "A").End(xlUp).Row 'loop through the rows of the source data
        For j = 2 To wsSource.Cells(i, Columns.Count).End(xlToLeft).Column 'loop through the columns of the source data
            If wsSource.Cells(i, j) <> "" Then 'check if the cell is not empty
                arr = Split(wsSource.Cells(i, j), "-") 'split the cell value by "-"
                wsTarget.Cells(k, "A") = wsSource.Cells(i, "A") 'copy the order ID
                wsTarget.Cells(k, "B") = arr(0) 'copy the item
                wsTarget.Cells(k, "C") = arr(1) 'copy the quantity
                k = k + 1 'increment the target row index
            End If
        Next j
    Next i
End Sub

To run this code, you need to open the Visual Basic Editor (Alt + F11), insert a new module, and paste the code. Then you can run the macro from the Developer tab or the Macro dialog box (Alt + F8).