Often, we need to convert columns to rows in excel based on cell value. This based on cell values conversion demands a criterion to be imposed on a cell or cells. In normal scenarios, transposing based on cell value or column to rows conversion (or vice versa) based on cell value is rarely required in any situation. However, the IF function converts columns to rows based on cell value. In this article, we demonstrate the conversion of columns to rows based on cell value. As well as we use a different dataset to transpose unique cell values to other columns.
Download Excel Workbook
2 Ways to Convert Columns to Rows in Excel Based On Cell Value
Method 1: IF and TRANSPOSE Functions to Convert Columns to Rows in Excel Based On Cell Value
Let’s say, we have a dataset where Employee Name, Salary, Allowance, and Total Salary are stored. For some reason, we need Employee Names in Columns and Salary and Allowance amounts in Rows. To do so, we use the Total Salary amount of an Employee to insert the range in transpose. Therefore, the IF and TRANSPOSE function is used to convert columns into rows based on a Total Salary value.
Step 1: Enter a criterion in any blank cell (i.e., G5). Paste the following array formula in cell B15.
The formula runs a logical_test (i.e., E5>G5) using the IF function. Then, if the logical_test returns TRUE, the formula executes the TRANSPOSE function for the range (i.e., B4:D13). Otherwise, the inserted formula displays a blank cell.
You can put any amounts (i.e., Individual Total Salary or Range Total Salary) as criteria to run the logical_test depending on cell value.
Step 2: Press CTRL+SHIFT+ENTER (for Excel 365, Use only the ENTER key) altogether to display the columns as rows as depicted in the following picture.
Transposing columns to rows depending on cell value is a seldom-used operation. As far as we know, only conditional functions such as IF allow Excel to do this.
- How to Transpose Duplicate Rows to Columns in Excel (4 Ways)
- Excel Power Query: Transpose Rows to Columns (Step-by-Step Guide)
- How to Transpose Rows to Columns Using Excel VBA (4 Ideal Examples)
- Transpose Multiple Columns into One Column in Excel (3 Handy Methods)
Method 2: Transpose Columns in Rows with Multiple Columns Based on Unique Values
Sometimes, we need values in different columns depending on their unique cell values. Though this is not the conventional transposing columns to rows, we demonstrate it to clarify the scenario: what are the ways we have to transpose values based on cell values.
For this method, we use a different dataset. In this dataset, we have Order Date, Product, and their sold Quantity. From the dataset, we can easily find out that the products have different sold quantities. And we want each unique sold quantity in columns.
Method 2.1: Formula to Transpose Columns Based on Unique Values
Step 1: Type the below array formula in any blank cell (i.e., F5).
=INDEX($C$5:$C$17, MATCH(0, COUNTIF($F$4:$F4, $C$5:$C$17), 0))
In the formula, the COUNTIF function counts the cell imposing criteria (i.e., $C$5:$C$17) in the range (i.e., $F$4:$F4). Then passes the count as lookup_array for the MATCH function. Finally, the outcome of the MATCH function is assigned as the row_num to the INDEX function.
Dollar Signs ($) before the cell references are there for absolute references.
Step 2: As the formula is an array formula, Hit CTRL+SHIFT+ENTER (for Excel 365, Use only the ENTER key) altogether to display the 1st product as shown in the image.
Step 3: Write the below formula in cell G5.
=IFERROR(INDEX($D$5:$D$17, MATCH(0, COUNTIF($F5:F5,$D$5:$D$17)+IF($C$5:$C$17<>$F5, 1,0), 0)), 0)
The formula declares the same arguments as Step 1 but imposes an extra condition using the IF function. The IF function executes a logical_test (i.e., $C$5:$C$17<>$F5) to make up the lookup_array for the MATCH function.
Step 4: Again, Press CTRL+SHIFT+ENTER (for Excel 365, Use only the ENTER key) to appear the unique value for product Bran.
Step 5: Drag the Fill Handle side way to display all the unique sole quantities for the product in F5.
Step 6: 2 different formulas are used to insert unique values in different columns. Use the Fill Handle to copy and insert formulas in other cells as depicted in the below picture.
From the above image, you see unique sold quantities of different unique products in different columns. This method is useful when you want to see the individual unique cell entries for each unique entry.
Method 2.2: VBA Macro Code to Transpose Cells to Columns Based on Unique Cell Values
VBA Macros are efficient in achieving criteria-based outcomes. Since our sought outcomes are unique values of unique products, we can use VBA Macros.
Step 1: To open the Microsoft Visual Basic window, press ALT+F11 altogether. The Microsoft Visual Basic window opens. In the Microsoft Visual Basic window, Select Insert (from the Toolbar) > Choose Module (from the options).
Step 2: In the Module, Paste the following macro code.
Sub Transpose_Unique() Dim mrfRow As Long Dim i As Long Dim mrfCrt As String Dim mrfCol As New Collection Dim mrfRng As Range Dim mrfOutRng As Range Dim mrfTxt As String Dim mrfCount As Long Dim mrfVRng As Range On Error Resume Next mrfTxt = ActiveWindow.RangeSelection.Address Set mrfRng = Application.InputBox("please select data range(maximum two columns):", "Select the range", mrfTxt, , , , , 8) Set mrfRng = Application.Intersect(mrfRng, mrfRng.Worksheet.UsedRange) If mrfRng Is Nothing Then Exit Sub If (mrfRng.Columns.Count <> 2) Or _ (mrfRng.Areas.Count > 1) Then MsgBox "the range an area with two columns ", , "Select the Cell" Exit Sub End If Set mrfOutRng = Application.InputBox("please select output range(specify one cell):", "Select the cell", mrfTxt, , , , , 8) If mrfOutRng Is Nothing Then Exit Sub Set mrfOutRng = mrfOutRng.Range(1) mrfRow = mrfRng.Rows.Count For i = 2 To mrfRow mrfCol.Add mrfRng.Cells(i, 1).Value, mrfRng.Cells(i, 1).Value Next Application.ScreenUpdating = False For i = 1 To mrfCol.Count mrfCrt = mrfCol.Item(i) mrfOutRng.Offset(i, 0) = mrfCrt mrfRng.AutoFilter Field:=1, Criteria1:=mrfCrt Set mrfVRng = mrfRng.Range("B2:B" & mrfRow).SpecialCells(xlCellTypeVisible) If mrfVRng.Count > mrfCount Then mrfCount = mrfVRng.Count mrfRng.Range("B2:B" & mrfRow).SpecialCells(xlCellTypeVisible).Copy mrfOutRng.Offset(i, 1).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True Application.CutCopyMode = False Next mrfOutRng = mrfRng.Cells(1, 1) mrfOutRng.Offset(0, 1).Resize(1, mrfCount) = mrfRng.Cells(1, 2) mrfRng.Rows(1).Copy mrfOutRng.Resize(1, mrfCount + 1).PasteSpecial Paste:=xlPasteFormats mrfRng.AutoFilter Application.ScreenUpdating = True End Sub
The macro declares multiple variables in their required types. Then displays a dialog box saying Select the range if the range contains the column number that does not equal 2. Again, bring out a dialog box to select the cell where you want to insert the transposed columns. At last, the macro copy and paste the cell entries changing their offsets using the VBA OFFSET function.
Step 3: Use the F5 key to run the macro. In a moment a dialog box named Select the range appears. Insert the range (i.e., C4:D17). Click on OK.
Step 4: Afterward another dialog box named Select the cell appears. Select the cell (i.e., F4) where you want your transpose column to start. Click on OK.
Step 5: In a moment, all the unique entries appear in different columns of respective unique products.
In this article, we demonstrate how the IF and TRANSPOSE function convert columns to rows in excel based on cell value. However, transposing cell values in another column based on unique values is also described due to the similarities in data type. Hope these methods guide you in the direction that your data type requires. Comment if you have further inquiries or have anything to add.
- How to Transpose Every n Rows to Columns in Excel (2 Easy Methods)
- Excel VBA: Transpose Multiple Rows in Group to Columns
- How to Transpose Rows to Columns Based on Criteria in Excel
- How to Transpose Rows to Columns Using Excel VBA (4 Ideal Examples)
- How to Transpose Rows to Columns in Excel (5 Useful Methods)
- VBA to Transpose Array in Excel (3 Methods)