How to Convert Columns to Rows in Excel Based On Cell Value

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.

Dataset-Convert Columns to Rows in Excel Based on Cell Value

Step 1: Enter a criterion in any blank cell (i.e., G5). Paste the following array formula in cell B15.

=IF(E5>G5,TRANSPOSE(B4:D13),"")

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.

IF and TRANSPOSE formula-Convert Columns to Rows in Excel Based on Cell Value

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.

Formula insertion

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.

Read More: How to Transpose Columns to Rows In Excel (6 Methods)


Similar Readings


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.

Dataset 2-Convert Columns to Rows in Excel Based on Cell Value


Method 2.1: Formula to Transpose Columns Based on Unique Values

A formula combined with the INDEX, MATCH, COUNTIF, and IF function can transpose the cell in another column 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.

Formula insertion

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.

Formula result

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.

Quantity formula

Step 4: Again, Press CTRL+SHIFT+ENTER (for Excel 365, Use only the ENTER key) to appear the unique value for product Bran.

Formula result

Step 5: Drag the Fill Handle side way to display all the unique sole quantities for the product in F5.

Fill handle

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.

Final formula result-Convert Columns to Rows in Excel Based on Cell Value

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).

vba-Convert Columns to Rows in Excel Based on Cell Value

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.

Select the range box

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.

Select the cell box

Step 5: In a moment, all the unique entries appear in different columns of respective unique products.

Final result-Convert Columns to Rows in Excel Based on Cell Value

Read More: VBA to Transpose Multiple Columns into Rows in Excel (2 Methods)


Conclusion

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.


Related Articles

Maruf Islam

Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo