How to Transpose Rows to Columns Based on Criteria in Excel (2 Ways)

Generally, The TRANSPOSE function may often be used to convert rows to columns. However, results depending on certain criteria, such as unique values, will not be returned. In this tutorial, we will show you how to transpose rows to columns based on criteria in Excel.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


2 Handy Approaches to Transpose Rows to Columns Based on Criteria in Excel

We’ve included a data set of several products and their quantities in the figure below. The rows will then be transposed into columns. We’ll transpose the rows into columns based on the criteria of the unique values because there are some duplicate entries in a separate cell. First, we’ll use the INDEX, MATCH, COUNTIF, IF, and IFERROR functions to create formulas. We’ll also execute a VBA code to accomplish the same thing.

Sample Data


1. Apply Formula with INDEX, MATCH, and COUNTIF Functions to Transpose Rows to Columns Based on Criteria in Excel

In the beginning, we will apply formulas of the INDEX, MATCH, COUNTIF, IF, and IFERROR functions with arrays.

Step 1: Insert the INDEX, MATCH, and COUNTIF Functions

  • In cell E5, type the following formula to get the unique products.
=INDEX($B$5:$B$12, MATCH(0, COUNTIF($E$4:$E4, $B$5:$B$12), 0))

Handy Approaches to Transpose Rows to Columns Based on Criteria in Excel

Step 2: Apply Array

  • To apply the formula with an array, press  Ctrl  + Shift + Enter 

Handy Approaches to Transpose Rows to Columns Based on Criteria in Excel

  • Therefore, you will get the first unique result.

Handy Approaches to Transpose Rows to Columns Based on Criteria in Excel

Step 3: Auto-Fill Cells

  • To get all the unique values, use the AutoFill Handle Tool to auto-fill the column.

Handy Approaches to Transpose Rows to Columns Based on Criteria in Excel

Step 4: Enter the IFERROR Functions

  • To transpose the row value of quantities into columns, write the following formula.
=IFERROR(INDEX($C$5:$C$12, MATCH(0, COUNTIF($E5:E5, $C$5:$C$12) + IF($B$5:$B$12<>$E5,1,0),0)),0)

Handy Approaches to Transpose Rows to Columns Based on Criteria in Excel

Step 5: Apply Array

  • To Insert an array, press  Ctrl Shift + Enter .

Handy Approaches to Transpose Rows to Columns Based on Criteria in Excel

  • As a consequence, cell F5 will show the first transposed value as in the image shown below.

Handy Approaches to Transpose Rows to Columns Based on Criteria in Excel

Handy Approaches to Transpose Rows to Columns Based on Criteria in Excel

  • Finally, auto-fill the rows with the AutoFill Handle Tool.
  • Therefore, all the transposed rows will transform into columns as in the image shown below.

Handy Approaches to Transpose Rows to Columns Based on Criteria in Excel

Read More: Transpose Multiple Rows in Group to Columns in Excel


Similar Readings


2. Run a VBA Code to Transpose Rows to Columns Based on Criteria in Excel

Step 1: Create a Module

  • Firstly, press  Alt F11  to start VBA Macro.
  • Click on Insert.
  • To create a Module, select the Module option.

Handy Approaches to Transpose Rows to Columns Based on Criteria in Excel

Step 2: Type VBA Codes

  • Paste the following VBA
Sub TransposeRows()
'Define Variables
    Dim RowsNumber As Long
    Dim p As Long
    Dim xColCr As String
    Dim xColumn  As New Collection
    Dim InputRng As Range
    Dim OutputRng As Range
    Dim RngText As String
    Dim CountRow As Long
    Dim xRowRg As Range
    On Error Resume Next
'Set values and Input Box fot Input Range
    RngText = ActiveWindow.RangeSelection.Address
    Set InputRng = Application.InputBox("Select Your Input Range for 2 columns:", "ExcelDemy", RngText, , , , , 8)
    Set InputRng = Application.Intersect(InputRng, InputRng.Worksheet.UsedRange)
'Apply Condition to count only 2 columns and to show msg box for alerting to select only two columns
    If InputRng Is Nothing Then Exit Sub
    If (InputRng.Columns.Count <> 2) Or _
       (InputRng.Areas.Count > 1) Then
        MsgBox "The specified range is only area for 2 columns ", , "ExcelDemy"
        Exit Sub
    End If
'Set values and Input Box fot Input Range
    Set OutputRng = Application.InputBox("Select Your Output Range (one cell):", "ExcelDemy", RngText, , , , , 8)
    If OutputRng Is Nothing Then Exit Sub
    Set OutputRng = OutputRng.Range(1)
'Count Rows
    RowsNumber = InputRng.Rows.Count
'Apply For loop
    For p = 2 To RowsNumber
        xColumn.Add InputRng.Cells(p, 1).Value, InputRng.Cells(p, 1).Value
    Next
    Application.ScreenUpdating = False
    For p = 1 To xColumn.Count
        xColCr = xColumn.Item(p)
        OutputRng.Offset(p, 0) = xColCr
        InputRng.AutoFilter Field:=1, Criteria1:=xColCr
        Set xRowRg = InputRng.Range("B2:B" & RowsNumber).SpecialCells(xlCellTypeVisible)
        If xRowRg.Count > CountRow Then CountRow = xRowRg.Count
'Copy and paste with transpose
        InputRng.Range("B2:B" & RowsNumber).SpecialCells(xlCellTypeVisible).Copy
        OutputRng.Offset(p, 1).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
        Application.CutCopyMode = False
    Next
    OutputRng = InputRng.Cells(1, 1)
    OutputRng.Offset(0, 1).Resize(1, CountRow) = InputRng.Cells(1, 2)
    InputRng.Rows(1).Copy
    OutputRng.Resize(1, CountRow + 1).PasteSpecial Paste:=xlPasteFormats
    InputRng.AutoFilter
    Application.ScreenUpdating = True
End Sub

Sample Data

Step 3: Run the Program

  • First, save and press  F5  to run the program.
  • Select your data set with the header.
  • Click on OK.

Sample Data

  • Select a cell to get the output
  • Then, click on OK.

Sample Data

  • As a result, you’ll obtain results that are transposed rows into columns, as shown in the image below.

Sample Data

Read More: How to Transpose Rows to Columns Using Excel VBA (4 Ideal Examples)


Conclusion

I hope this article has given you a tutorial about how to transpose rows to columns based on criteria in Excel. All of these procedures should be learned and applied to your dataset. Take a look at the practice workbook and put these skills to the test. We’re motivated to keep making tutorials like this because of your valuable support.

If you have any questions – Feel free to ask us. Also, feel free to leave comments in the section below.

We, the Exceldemy Team, are always responsive to your queries.

Stay with us & keep learning.


Related Articles

 

Bhubon Costa

Bhubon Costa

Myself, Bhubon Costa, an engineer, is currently employed with Exceldemy as an Excel & VBA Content Developer. I appreciate solving analytical difficulties, finding practical answers, and, for the most part, I enjoy taking on new tasks. I write articles on Exceldemy about various ways to get out of Microsoft Excel's stuck conditions. My passion is to leave my mark on the world through my work and to have an impact on the community who benefit from it.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo