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.
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))
Step 2: Apply Array
- To apply the formula with an array, press Ctrl + Shift + Enter
- Therefore, you will get the first unique result.
Step 3: Auto-Fill Cells
- To get all the unique values, use the AutoFill Handle Tool to auto-fill the column.
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)
Step 5: Apply Array
- To Insert an array, press Ctrl + Shift + Enter .
- As a consequence, cell F5 will show the first transposed value as in the image shown below.
- Drag down with the AutoFill Handle Tool to auto-fill the column.
- 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.
- How to Transpose Duplicate Rows to Columns in Excel (4 Ways)
- Excel VBA: Transpose Multiple Rows in Group to Columns
- Transpose Multiple Columns into One Column in Excel (3 Handy Methods)
- How to Reverse Transpose in Excel (3 Simple Methods)
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.
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
Step 3: Run the Program
- First, save and press F5 to run the program.
- Select your data set with the header.
- Click on OK.
- Select a cell to get the output
- Then, click on OK.
- As a result, you’ll obtain results that are transposed rows into columns, as shown in the image below.
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.
- Convert Columns to Rows in Excel Using Power Query
- How to Convert Columns to Rows in Excel Based On Cell Value
- VBA to Transpose Multiple Columns into Rows in Excel (2 Methods)
- How to Convert Column to Comma Separated List With Single Quotes
- Excel Paste Transpose Shortcut: 4 Easy Ways to Use
- Excel Transpose Formulas Without Changing References (4 Easy Ways)
- How to Transpose in Excel VBA (3 Methods)